autoexcrement (6/6/2014)
Sorry Lynn, I wasn't sure DDL was necessary in this case.Yes, we are importing data from an external source into Scratch, scrubbing it for dupes again Live, and then inserting only the new and modified records to Live.
Unfortunately the following from your original post doesn't really help much:
;WITH CTE AS
(
SELECT
Col1, -- bit
Col2, -- int
Col3, -- varchar
Col4, -- varchar
Col5, -- varchar
Col6, -- varchar
Col7, -- varchar
Col8 -- varchar
FROM Scratch
INTERSECT
SELECT
Col1, -- bit
Col2, -- int
Col3, -- varchar
Col4, -- varchar
Col5, -- varchar
Col6, -- varchar
Col7, -- varchar
Col8 -- varchar
FROM Live
)
DELETE FROM CTE
-- and then insert the remaining records from Scratch to Live
I gather the first alternative here is to do a WHERE EXISTS query.
DELETE FROM Scratch AS S
WHERE EXISTS
(
SELECT 1
FROM Live AS L
WHERE
L.Col1 = S.Col1 AND
L.Col2 = S.Col2 AND
L.Col3 = S.Col3 AND
L.Col4 = S.Col4 AND
L.Col5 = S.Col5 AND
L.Col6 = S.Col6 AND
L.Col7 = S.Col7 AND
L.Col8 = S.Col8
)
What are Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8? I don't mean the data types, but what are they? Are any of these columns a unique or primary key value for the data? This is information that would help in providing you with good answers.
You also mentioned that there are other columns in the data that aren't used in the comparison. What is done with those values? Are they ignored, do they update their respective columns if the columns actually compared are updated in the Live table?