sql合并数据(循环更新数据)

     SQL数据的合并与 循环更新数据 在开发当中用的比较经常  前几天刚好有同学问到此类问题 在提到循环操作数据时  很多人都会想到递归 呵呵  这里就不说它了 

递归帮助地址：ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/udb9/html/4acf8a3e-6dcc-420c-9088-9c57b976113e.htm

但有此时候递归不适合我们的须求 在这就记录一下我个人的写法   如果还有好方法，望留言相互学习 例子如下：

     例：根据某须求须要  在将任务分给几个不同的人去完成(称执行员)   而且执行员没有权限(或没有环境)直接操作数据   这时给执行人员每人一个简易的数库 等都执行完成后
同一合并母库中 如果库中的表存在主外键(或其它条件)时 对合并数据时会带来麻烦 我将在这共享我自己的SQL写法 

  

例1 代码

declare @count int , @index int , @userID int , @newid int
set @index = 1
set @count = ( select count (UserID) from Users where UserID >= 12064 )
set @userID = 12064
print @count
while ( @index <= @count )
begin
begin transaction
insert into 母库.Users
(ID,UserName, [ Password ] ,Gender,Email,RegisterDate,RegisterIP,LastOnline,LastIP,CookieExpireDate,SubjectQty,ReplyQty, [ State ] )
select ID,UserName, [ Password ] ,Gender,Email,RegisterDate,RegisterIP,LastOnline,LastIP,CookieExpireDate,SubjectQty,ReplyQty, [ State ]
from ( select top 1 UserID,CommunityID,UserName, [ Password ] ,Gender,Email,RegisterDate,RegisterIP,LastOnline,LastIP,CookieExpireDate,SubjectQty,ReplyQty, [ State ]
from 子库.Users where UserID >= @userID order by UserID asc ) as usertable

set @newid = @@identity
print @newid
insert into 母库.UserInfos
(UserID,Avatar, [ Signature ] ,TaskID,BrandPreference,BrandDisgust,LastCookie)
select @newid as UserID,Avatar, [ Signature ] ,TaskID,BrandPreference,BrandDisgust,LastCookie
from ( select top 1 UserID,Avatar, [ Signature ] ,TaskID,BrandPreference,BrandDisgust,LastCookie
from 子库.UserInfos where UserID >= @userID order by UserID asc ) as tableuser

set @index = @index + 1
set @userID = ( select top 1 userid from NM.dbo.CommunityUsers where UserID > @userID order by UserID)
if ( @@error = 0 )
commit transaction
else
rollback transaction
end
GO

 

例二:  根据以上的方法也可用到数据的循环更新数据上  例如:TempTicketDetail表中的通一个订单按OriginalQty + VarientQty=当前的值，一个订单多条记录时第一笔是OriginalQty + VarientQty=当前的值,第二笔是第一笔的当前值+第二笔的VarientQty，以此类推

表结构如下

用以上方法的SQL如下：

 

代码

declare @count int , @orderid1 int , @index int , @result int
declare @count_1 int , @id_1 int , @index_1 int , @result_1 int , @valueCount int
set @index = 0
set @count = ( select count ( distinct orderid) from TempTicketDetail where orderid > - 1 )

set @orderid1 = ( select distinct top 1 orderid from TempTicketDetail where orderid > - 1 order by orderid)

while ( @index < @count )
begin
set @index_1 = 0
set @valueCount = ( select count ( [ id ] ) from TempTicketDetail where orderid = @orderid1 )
set @id_1 = ( select top 1 [ id ] from TempTicketDetail where orderid = @orderid1 order by [ id ] )
if ( @valueCount = 1 )
begin
set @result = ( select (OriginalQty + VarientQty) as hh from TempTicketDetail where [ Id ] = @id_1 )
update TempTicketDetail set CurrentQty = @result where [ Id ] = @id_1
set @id_1 = ( select top 1 [ id ] from TempTicketDetail where orderid = @orderid1 and [ id ] > @id_1 order by [ id ] )
end
else
begin
declare @firstid int
set @firstid = 1
while ( @index_1 < @valueCount )
begin
if ( @firstid = 1 )
begin
set @result = ( select (OriginalQty + VarientQty) as hh from TempTicketDetail where [ Id ] = @id_1 )
update TempTicketDetail set CurrentQty = @result where [ Id ] = @id_1
set @id_1 = ( select top 1 [ id ] from TempTicketDetail where orderid = @orderid1 and [ id ] > @id_1 order by [ id ] )
end
else
begin
set @result = @result + ( select VarientQty from TempTicketDetail where [ Id ] = @id_1 )
update TempTicketDetail set CurrentQty = @result where [ Id ] = @id_1
set @id_1 = ( select top 1 [ id ] from TempTicketDetail where orderid = @orderid1 and [ id ] > @id_1 order by [ id ] )
end
set @firstid = 2
set @index_1 = @index_1 + 1
end
end
set @orderid1 = ( select distinct top 1 orderid from TempTicketDetail where orderid > @orderid1 and orderid > - 1 order by orderid)
set @index = @index + 1
end

 

 

 

 

转载于:https://www.cnblogs.com/kaixun001/archive/2009/12/23/1630760.html