Let's say you want to transfer an amount of data from one database or table to another and you can't just transfer all the records without checking. You can do several statements after each other and do an INSERT IF NOT EXISTS, or you can do everything in one statement with MERGE.
Here is an example:
MERGE users AS t_U
USING (
SELECT [username], [password], [email], [fullname]
FROM
[someothertable]
WHERE
[isDeleted] = 'N'
) AS U
ON t_U.[username] = U.[username]
AND t_U.[email] = U.[email]
WHEN MATCHED THEN UPDATE
SET [is_duplicate] = 'Y'
WHEN NOT MATCHED THEN
INSERT(
[username], [password], [email], [fullname]
)
VALUES (
U.[username], U.[password], U.[email], U.[fullname]
);
GO
In this code I merge the users from someothertable to the users table. If the username and email are the same then I set the column is_duplicate on the target tabel to Y.
You can expand the WHEN MATCHED statement with extra criteria using AND X = Y or something like that, or you can add an extra WHEN MATCHED statement. The order of the WHEN MATCHED statements are important.
Used in Microsoft SQL server 2005 and higher
