I have a temporary input table with 3 indexes to make each row unique.
I need to carry out a compare with another table (Say, TableB) with the same table format and indexes, in view to inserting rows from the temp table to tableB when there is no match using the indexes, else carry out field updates from the temp table
What is the best method of carrying out these tow sets of compares?
Many thanks
MERGE, or you could do separate UPDATE and INSERT
UPDATE T
SET Col1 = S.Col1,
Col2 = S.Col2,
...
FROM SourceTable AS S
JOIN TargetTable AS T
ON T.PKeyCol1 = S.PKeyCol1
AND T.PKeyCol2 = S.PKeyCol2
...
INSERT INTO TargetTable
(
PKeyCol1, PKeyCol2, Col1, Col2, ...
)
SELECT S.PKeyCol1, S.PKeyCol2, S.Col1, S.Col2, ...
FROM SourceTable AS S
LEFT OUTER JOIN TargetTable AS T
ON T.PKeyCol1 = S.PKeyCol1
AND T.PKeyCol2 = S.PKeyCol2
WHERE T.PKeyCol1 IS NULL -- Target Row does not exist