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

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

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

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

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

  

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