If exists UPDATE else INSERT for each row of a table

Question

I have a table named tableFrom want to insert into a table named tableTo. The insert worked well, but if I insert the same values again, I have a duplicate key error. So I want to update only the rows already existing. I know the command ON DUPLICATE with MySQL, unfortunately missing in SQL Server.

If I want to only check for only one precise row, it is easy:

IF EXISTS PK = @PK

But I am trying to do it for a whole table, and I don't know if it is possible. I thought of cheking each row with cursor, I am new to SQL.

Here is what I came up with:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
IF EXISTS   (
            SELECT 
                1 
            FROM 
                tableFrom F,
                tableTo T
            WHERE 
                T.product = F._product
            )
    BEGIN
        UPDATE
            tableTo
        SET
            T.something = F.something
        FROM
            tableTo T
                INNER JOIN 
                    tableFrom F
                ON
                    T.product = F._product
    END
ELSE
    BEGIN
        INSERT INTO tableTo
            (product, 
            something)
        SELECT
            F._product, 
            F.something
        FROM
            tableFrom F
    END
COMMIT TRANSACTION

The UPDATE part is working fine, but no INSERT done.

EDIT1:

Tried this code:

MERGE tableTo AS T
USING tableFrom AS S
    ON (T.product= S._product)
WHEN NOT MATCHED BY TARGET
    THEN INSERT(product, something) VALUES(S._product, S.something)
WHEN MATCHED 
    THEN UPDATE SET T.Something= S.Something

Have the following error: "Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE."

EDIT2:

I googled the above error message and it appeared to be due to a missing semi-colon at the end of the very last line before the MERGE statement. The MERGE command is working perfect!

Answer 1

It's not missing. SQL Server implements the standard MERGE statement which allows you to specify what happens when a match occurs or not between a source and a target. Check the documentation for examples.

Matches are made using a condition that can involve many columns. MERGE allows you to execute an INSERT, UPDATE or DELETE in the following cases:

• A match is found based on the condition
• A match occurs only in the source
• A match occurs only in the target

This way you can update existing rows, insert rows that exist only in the source, delete rows that appear only in the target.

In your case, you could do something like:

MERGE tableTo AS T
USING tableFrom AS S
      ON (T.product= S._product)
WHEN NOT MATCHED BY TARGET
     THEN INSERT(product, something) VALUES(S._product, S.something)
WHEN MATCHED 
     THEN UPDATE SET T.Something= S.Something
OUTPUT $action, Inserted.*, Deleted.*;

This statement will insert or update rows as needed and return the values that were inserted or overwritten with the OUTPUT clause.

Answer 2

Use the Merge operation.

From the MSDN documentation:

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.