create table time (
t1 varchar(32) unique,
t2 varchar(32) unique,
js int
);
create table err (
ddbh varchar(32),
sj datetime default getdate()
);
declare @d datetime
set @d = getdate();
while @d > '2018-01-01'
begin
insert into time values
(concat(CONVERT(varchar(100), @d, 23), ' 00:00:00.000'), concat(CONVERT(varchar(100), @d, 23), ' 23:23:59.999'),
'0');
set @d = DATEADD(dd, -1, @d);
print @d;
end
select * from time;
update time set js ='0';
select * from QP_ORDER_DATA_DETAIL_BAK;
while 1 = 1
begin
declare @t1 varchar(32), @t2 varchar(32), @js int;
select top 1
@t1 = t1,
@t2 = t2,
@js = js
from time
where js = 0
order by t1 desc;
print '开始 ' + @t1;
DECLARE rs CURSOR LOCAL SCROLL FOR select ORDER_ID，spmc from dbosql_1.dbo.QP_ORDER_DATA_BAK where TRANSACTION_TIME>=@t1 and TRANSACTION_TIME<=@t2;
DECLARE @ORDER_ID varchar(64),@spmc varchar(64);
OPEN rs
FETCH NEXT FROM rs INTO @ORDER_ID,@spmc;
WHILE @@FETCH_STATUS = 0
BEGIN
Begin Try
Begin TransAction;
if NOT exists(SELECT 1 from dbosql.dbo.QP_ORDER_DATA_DETAIL_BAK where ORDER_ID =@ORDER_ID)
insert into dbosql.dbo.QP_ORDER_DATA_DETAIL_BAK
select * from dbosql_1.dbo.QP_ORDER_DATA_DETAIL_BAK where ORDER_ID =@ORDER_ID;
commit TransAction;
End Try
Begin Catch
Rollback TransAction
insert into err (ddbh) values (@ORDER_ID);
print @ORDER_ID;
End Catch
FETCH NEXT FROM rs INTO @ORDER_ID,@spmc;
END
CLOSE rs
DEALLOCATE rs
update dbosql.dbo.time set js ='1' where t1=@t1 and t2=@t2;
print '结束 ' + @t2;
end
