Reputation: 2472
The scenario is typical. User asks me to import a spreadsheet into a table - Mastertable.
Mastertable has a PK of acct and FK of ProviderID.
The import goes poorly, about 10% of records lost due to key violations. I think this is due to specifying a providerID that does not exist.
Is there a way I can figure out which providerIDs are bad? I can import into a fresh table - "MasterTableNew", but when I do a select like this:
SELECT acct FROM MastertableNew
WHERE NOT EXIST (
SELECT Mastertable.acct
FROM Mastertable
INNER JOIN MastertableNew
ON Mastertable.acct = MastertableNew.acct)
it turns up blank - I am expecting to see rows that exist in MastertableNew that do not exist in Mastertable.
Upvotes: 0
Views: 61
Reputation: 91376
How about:
SELECT acct FROM MastertableNew
LEFT JOIN Mastertable ON MastertableNew.acct = Mastertable.acct
WHERE Mastertable.acct Is Null
Upvotes: 1