Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Access
Starting Member
44 Posts |
 Posted - 2011-05-15 : 23:48:41
|
| 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 inTableBMy question: Is there an anyway I can do it somehow using MERGE statement? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-05-16 : 03:33:28
|
You can't have same row do dual operation in target table.So... Just do a deleteDELETE a FROM dbo.TableA AS a INNER JOIN dbo.TableB AS b ON b.TrialID = a.TrialIDNext step is to do a normal insertINSERT dbo.TableA (col list here)SELECT (col list here) FROM dbo.TableB
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
Access
Starting Member
44 Posts |
Posted - 2011-05-16 : 07:41:14
|
quote:
Originally posted by Peso
You can't have same row do dual operation in target table.So... Just do a deleteDELETE a FROM dbo.TableA AS a INNER JOIN dbo.TableB AS b ON b.TrialID = a.TrialIDNext step is to do a normal insertINSERT 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 |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-16 : 07:55:03
|
| 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. |
|
|
|
|
|
|
Help with MERGE statement