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 |
|
kctechnoproG
Starting Member
1 Post |
 Posted - 2009-09-25 : 09:54:56
|
| Anyone have a way to make this work better with Intesect and Except???Details:If you are using the INTERSECT or EXCEPT in such queries shown below,UPDATE CustomersA FROM (CustomersB INTERSECT CustomersA)goINSERT INTO CustomersA FROM (CustomersB EXCEPT CustomersA)goYou will still fail with the error messageIncorrect syntax near the keyword 'FROM'. Instead you can still run 2 queries to get the same upsert functionality, first run an update command for the rows that exist in both record sets or tables then an insert command for the records that does not exist in the target row set or the table.UPDATE CustomersA SET CustomerName = B.CustomerNameFROM CustomersA A (NoLock)INNER JOIN CustomersB B (NoLock) ON A.CustomerId = B.CustomerId And later run the Insert commandINSERT INTO CustomersA ( CustomerId, CustomerName)SELECT Id, NameFROM CustomersB (NoLock)WHERE Id NOT IN ( SELECT CustomerId FROM CustomersA (NoLock) )- Rick Pressler |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-25 : 10:33:31
|
>>Anyone have a way to make this work better with Intersect and Except???By "work better" you mean not error out?  You are not using intersect/except correctly. Like UNION ALL you need to intersect (or except) two statements - not two tables.I haven't used those in production yet because my intuition is that it won't be any faster than inner join (for intersect) or a left outer join (for except). But that is not based on any testing just intuition.Be One with the OptimizerTG |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-25 : 14:25:29
|
| I also prefer using JOIN rather than INTERSECT,EXCEPT |
|
|
|
|
|
|
UPSERT LOGIC