Search code examples
sql-servermergebulkupdate

How to do bulk update using the Below query

Merge DBO1..tblinventoryStock_Targer as T

Using   DBO2..tblinventoryStock_Source as S

on S.Inventorycode =T.Inventorycode  and 

S.Locationcode =T.Locationcode

when matched then 
update set T.QtyOnhand  = S.QtyonHand,
T.Modifydate=S.Modifydate,
T.QtySold = S.QtySold  

when not matched by Target then
insert  ( --Fields)
values(--Values);

I have to insert this from DB2 to DB1 in sql server i have some one million of record . How to do the update and insert quickly it takes me a 5 min to execute the query via C# using stored procedure

Solution

  • When you do an UPDATE that will potentially affect a million rows, it is best to do it in batches. Try batches of 50,000 rows at a time.