SQLTeam.com | Weblogs | Forums

Adding new rows in table with multiple indexes


#1

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


#2

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