Batch Update Sample


  

 
  create table test (dwJobItemId int,dwJobNumber int, dwJobItemNumber int, szPalletLineNumber varchar(10))
Insert into test  values(3123,3061,1,'0248466')
,(3124,3062,1,'0248955')
,(3486,3062,2,null) 
,(3199,3062,3,null) 
,(3129,3067,1,'0494795')
,(3504,3067,2,'')

 

  
declare @chunk int=1, @i int =1
  
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION
          
    ;With mycte as
    (
    Select dwJobItemId ,szPalletLineNumber, Max(szPalletLineNumber) Over(Partition by  dwJobNumber )  maxszPalletLineNumber, 
	row_number() Over(Order by dwJobItemId) rn from test
  
    )
 
 
    ,mycte1 as (
    Select * from mycte 
    Where rn between  @chunk*(@i-1)+1 and  @chunk*@i)
 
    Merge Top (@chunk) mycte1 a
    Using test b on A.dwJobItemId=B.dwJobItemId
    When matched   then
    Update
   Set A.szPalletLineNumber=maxszPalletLineNumber;
   
   
    IF @@ROWCOUNT = 0  
      BEGIN
        COMMIT TRANSACTION
        BREAK
      END
 
 Set @i=@i+1
  
    COMMIT TRANSACTION
  
END


Select * from test
Order by dwJobNumber, dwJobItemNumber

drop table test


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s