June 9, 2008 at 2:48 pm
Hello All,
Consider two identical tables.
One table is hosted locally on SQL Server 2005, the other is hosted remotely, on MySQL... don't ask why.
I want to INSERT rows into the remote table, from the local table, but only want to insert rows that don't exist already.
(I don't really want to get into how I'm doing the INSERTs here, but if anyone's curious, I'm updating via an SP that uses INSERT OPENQUERY and a linked server using the MySQL ODBC.)
If the Primary key wasn't compound, I'd use the following to check for existence (let's assume that CompanyID is the Primary Key):
INSERT RemoteTable (CompanyID, CategoryID)
SELECT CompanyID, CategoryID
FROM LocalTable AS LT
WHERE LT.CompanyID NOT IN (SELECT CompanyID FROM RemoteTable)
However, I'm stuck as to how to check for existence when the primary key is compound (i.e. CompanyID and CategoryID make-up the uniqueness of the row).
As always, and thoughts/help/input would be greatly appreciated.
Thanks,
Simon
June 9, 2008 at 2:54 pm
Like this:
INSERT RemoteTable (CompanyID, CategoryID)
SELECT CompanyID, CategoryID
FROM LocalTable AS LT
WHERE NOT EXISTS (SELECT * FROM RemoteTable RT
WHERE Rt.CompanyID = LT.CompanyID
And RT.CategoryID = LT.CategoryID)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 3:00 pm
You're the man!
Thanks so much - I really appreciate it.
-Simon
June 9, 2008 at 3:09 pm
Simon Doubt (6/9/2008)
You're the man!Thanks so much - I really appreciate it.
-Simon
Funny, my kids call me "The Man" also, but I don't think it's a compliment when they say it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply