Batch Update with Merge SampleT-SQL


  

-----Set up
--create table TabA (Accountkey int  identity(1,1), IPKey int, [StartDateTime] datetime)
--go
--insert into TabA ([StartDateTime])  values(getdate())
--go 200000

--create table TabB (aAccountkey int  identity(1,1), IPKey int, [StartDateTime] datetime)
--go
-- insert into TabB ([StartDateTime])  values(getdate())
--go 21000


----drop table taba,tabb
 


  

  
declare @chunk int=5000, @i int =1
 
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION
		 
    ;With mycte as 
	(
	Select Accountkey,IPKey,StartDateTime , row_number() Over(Order by [StartDateTime]) rn from TabA a
	where [StartDateTime]  BETWEEN  current_timestamp-10 AND  current_timestamp+10
	)


	,mycte1 as (
	Select * from mycte 
	Where rn between  @chunk*(@i-1)+1 and  @chunk*@i)

	Merge Top (@chunk) mycte1 a
	Using TabB b on A.Accountkey=B.aAccountkey
	When matched   then 
	Update 
   Set A.IPKey=Isnull(B.IPKey,-1);
  
  
    IF @@ROWCOUNT = 0  
      BEGIN
        COMMIT TRANSACTION
        BREAK
      END

 Set @i=@i+1
 
    COMMIT TRANSACTION
 
END
 

 --select * from taba



 
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