alter procedure Update_erp_ttadi00
as
begin
begin transactiondeclare @v_item_seq int,@i int
set @i = 0--声明游标
declare erp_ttadi00_q cursor
for
select item_seq from cbc0_erp.dbo.ttadi00 order by item_seq asc--打开游标
open erp_ttadi00_qfetch next from erp_ttadi00_q into @v_item_seq
while @@fetch_status = 0
begin
set @i = @i + 1 update cbc0_erp.dbo.ttadi00
set item_seq = 90000 + @i
where item_seq = @v_item_seq
update cbc0_erp.dbo.ttadt01
set item_seq = 90000 + @i
where item_seq = @v_item_seq update cbc0_erp.dbo.ttadt02
set item_seq = 90000 + @i
where item_seq = @v_item_seq
print @i
print @v_item_seq
fetch next from erp_ttadi00_q into @v_item_seq
end--关闭游标
close erp_ttadi00_q--释放游标
deallocate erp_ttadi00_qif @@error>0
rollback
else
commit
end
as
begin
begin transactiondeclare @v_item_seq int,@i int
set @i = 0--声明游标
declare erp_ttadi00_q cursor
for
select item_seq from cbc0_erp.dbo.ttadi00 order by item_seq asc--打开游标
open erp_ttadi00_qfetch next from erp_ttadi00_q into @v_item_seq
while @@fetch_status = 0
begin
set @i = @i + 1 update cbc0_erp.dbo.ttadi00
set item_seq = 90000 + @i
where item_seq = @v_item_seq
update cbc0_erp.dbo.ttadt01
set item_seq = 90000 + @i
where item_seq = @v_item_seq update cbc0_erp.dbo.ttadt02
set item_seq = 90000 + @i
where item_seq = @v_item_seq
print @i
print @v_item_seq
fetch next from erp_ttadi00_q into @v_item_seq
end--关闭游标
close erp_ttadi00_q--释放游标
deallocate erp_ttadi00_qif @@error>0
rollback
else
commit
end
select item_seq into #T from cbc0_erp.dbo.ttadi00
declare erp_ttadi00_q cursor
for select item_seq from #T order by item_seq asc
我刚注册没有分,但是感谢一定要表示。
估计的表中存在相同的值 ,或因表过大+90000后出现重复值,导致数据ASC排序过程中,对相同的记录进行了更新
+90000后也不会重复值,因为原表最大的item_seq没有大于90000
insert into @a select 'a',null
insert into @a select 'a',null
insert into @a select 'a',null
insert into @a select 'a',nulldeclare @b table(a varchar(10),i int)
insert into @b select 'a',null
insert into @b select 'a',null
insert into @b select 'a',null
insert into @b select 'a',null
declare @c table(a varchar(10),i int)
insert into @c select 'a',null
insert into @c select 'a',null
insert into @c select 'a',null
insert into @c select 'a',nulldeclare @i int
set @I=0
update @a set i=9000+@i,@i=@i+1
set @I=0
update @b set i=9000+@i,@i=@i+1
set @I=0
update @c set i=9000+@i,@i=@i+1select * from @a
select * from @b
select * from @c