MERGE statement to update or insert rows into a table-CodePudding

My task is to insert or update rows in a table2. Table1 contains id's of all employees. That id matches the ID in the table2. Some of the employees in table2 already have the rows I need but some don't. Table2 doesn't contain the ID's of the employees that don't have those rows.

My task is to update the rows for the existing ID's and insert for the ones that don't have those rows.

I have tried the following statement:

MERGE INTO dbo.table2 AS TGT
USING (SELECT table1ID FROM dbo.table1) AS SRC
      ON SRC.table1ID = TGT.table2ID

WHEN MATCHED 
     AND table2Code = 'ValueToInsertOrUpdateCode'
    THEN
       UPDATE 
       SET table2Value= 'ValueToInsertOrUpdateValue'

WHEN NOT MATCHED BY TARGET 
    THEN
        INSERT (table2Code, table2ID, table2Value)
        VALUES ('ValueToInsertOrUpdateCode', src.table1ID, 'ValueToInsertOrUpdateValue');

This currently only updates the rows that exist, but doesn't insert the rows for ID's that don't have existing rows.

CodePudding user response：

Based on your comments is sounds like you want this so that the WHEN NOT MATCHED BY TARGET is executed:

MERGE INTO dbo.table2 AS TGT
USING (SELECT table1ID FROM dbo.table1) AS SRC
      ON (SRC.table1ID = TGT.table2ID AND AND table2Code = 'ValueToInsertOrUpdateCode') -- This is the difference

WHEN MATCHED 
     AND table2Code = 'ValueToInsertOrUpdateCode'
    THEN
       UPDATE 
       SET table2Value= 'ValueToInsertOrUpdateValue'

WHEN NOT MATCHED BY TARGET 
    THEN
        INSERT (table2Code, table2ID, table2Value)
        VALUES ('ValueToInsertOrUpdateCode', src.table1ID, 'ValueToInsertOrUpdateValue');

WHEN NOT MATCHED BY TARGET would not execute when SRC.table1ID = TGT.table2ID (i.e. they match).

Updating the ON clause to ON (SRC.table1ID = TGT.table2ID AND AND table2Code = 'ValueToInsertOrUpdateCode') will give you the inserts you are expecting.

However you should probably not do this:
ON <merge_search_condition> Caution

It's important to specify only the columns from the target table to use for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Don't attempt to improve query performance by filtering out rows in the target table in the ON clause; for example, such as specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.

For this reason and what others have suggested it would be safer to do separate update and insert statements.

CodePudding user response：

I would, honestly, suggest avoiding the MERGE operator and doing an Upsert here instead. For your scenario, what you need is most likely the following:

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE T2 WITH (UPDLOCK, SERIALIZABLE) 
SET table2Value = 'ValueToInsertOrUpdateValue'
FROM dbo.Table2 T2
     JOIN dbo.Table1 T1 ON T1.table1ID = T2.table2ID;
-- You could honestly use an EXISTS here, considering that you're updating the table
-- with a literal, rather than a value from the table Table1.

INSERT INTO dbo.Table2 (table2Code , table2ID, table2Value)
SELECT 'ValueToInsertOrUpdateCode',
       T1.table1ID,
       'ValueToInsertOrUpdateValue'
FROM dbo.Table1 T1
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.Table2 T2
                  WHERE T2.table2ID = T1.table1ID);

COMMIT;

db<>fiddle