-1
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

Renganathan Rennie
  • 1
  • 4
  • So you have a c# function that does the job? Please post it. this show what you tried so far. providng some pseudo-code and expecting to get a runnable solution is not what stackoverflow is intended. as you're new here, this is a good read: https://stackoverflow.com/help/how-to-ask – Jan Oct 09 '18 at 06:50
  • Try to use incremental data update strategy using some columns like Modified date. Which merge only newly updated records with target not scan entire table. And validate proper index is use while running the query. – NP007 Oct 09 '18 at 08:00

1 Answers1

0

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.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52