ID | intTrialID | strLocationName | strLocationCity | strLocationState | srtLocationZip | strLocationCountry 1 | 1 | University Hospital | Phoenix | Arizona | 11111 | United States 2 | 1 | Hospital San Joao | Leuven | | | Belgium 3 | 1 | Research Site | Camperdown | New South Wales | | Australia 4 | 2 | Medical Center | Nashville | Tennessee | 66666 | United States 5 | 1 | Research Site | Toronto | | | Canada 6 | 2 | Monteluce | Barcelona | | | Spain 7 | 2 | Medical Center | Dallas | Texas | 77777 | United States
Here I have TableB that I populate on the fly and it stores single intTrialID at a time.
intTrialID | strLocationName | strLocationCity | strLocationState | srtLocationZip | strLocationCountry 1 | GSK Site | Glasgow | Lanarkshire | G21 3UW | United Kingdom 1 | St. Helier NHS | Carshalton | Surrey | SM5 1AA | United Kingdom 1 | University Hospital | Tucson | Arizona | 22222 | United States
Basically what I need to do is delete corresponded intTrialID from TableA , then insert into TableA whatever is inTableB
My question: Is there an anyway I can do it somehow using MERGE statement?
You would normally join using the intTrialID (looks like trieal id and location name) and update if it was there (comparing the attributes) or insert if it wasn't. If you really want a delete and insert you could use a cte to duplicate your tableA with an attibute I for insert and D for delete. Then you can use this attribute value = D to delete rows that exist and insert everything with attibute I.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.