Geek Answers Handbook

Filling a table with records from an intermediate table

I have 2 identical tables in SQL Server 2008, one of them is the "Live" table, which my application is constantly using. I periodically shoot new records throughout the day and insert them into the "setting" table, from which I need to pull out new records and paste them into the Live table. I do not want any duplicates inserted only if some records overlap. There are 10 columns that I need to look to see if an identical record exists, I looked at some TSQL examples, but nothing works yet, I also looked at dealing with cheats and just reporting DISTINCT values, but DISTINCT one works for one record, I need her to work at 10. Any suggestions?

Thanks Sam

+5
source share
1 answer

This is an ideal use case for NOT EXISTS- you can check as many criteria as you want to make sure that you are not cheating.

INSERT INTO Live
SELECT <fields>
FROM Staging s
WHERE NOT EXISTS (SELECT 1
                  FROM Live l
                  WHERE s.FieldA = l.FieldA
                  AND s.FieldB = l.FieldB
                  <all your checks here>...)
+5
source

More articles:


All Articles