SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with MERGE statement		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Access
Starting Member

38 Posts
Posted - 05/15/2011 :  23:48:41  Show Profile  Reply with Quote
TableA

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?

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 05/16/2011 :  03:33:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can't have same row do dual operation in target table.

So... Just do a delete
DELETE a FROM dbo.TableA AS a INNER JOIN dbo.TableB AS b ON b.TrialID = a.TrialID

Next step is to do a normal insert

INSERT dbo.TableA (col list here)
SELECT (col list here) FROM dbo.TableB


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Access
Starting Member

38 Posts
Posted - 05/16/2011 :  07:41:14  Show Profile  Reply with Quote
quote:
Originally posted by Peso

You can't have same row do dual operation in target table.

So... Just do a delete
DELETE a FROM dbo.TableA AS a INNER JOIN dbo.TableB AS b ON b.TrialID = a.TrialID

Next step is to do a normal insert

INSERT dbo.TableA (col list here)
SELECT (col list here) FROM dbo.TableB


N 56°04'39.26"
E 12°55'05.63"


This is basically what I thought, but was looking for other alternatives.

Thanks
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 05/16/2011 :  07:55:03  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000