How to use MSSQL MERGE INTO

Using MERGE INTO allows you to execute insert, update, or delete operations on the target TABLE, usually as a result of a join with the source TABLE.

MERGE INTO Features

• The MERGE statement allows you to perform insert, update, or delete operations on the target table.
• You can control whether to enter or modify the table according to the conditions.
• It’s just SQL, which is very efficient for synchronizing data between two tables.

MERGE INTO basic structure

MERGE INTO syntax

MERGE INTO [Tarket table] AS T1
USING [Source table] AS S1
ON T1.[COLUMN1] = S1.[COLUMN1]
WHEN MATCHED THEN 
    UPDATE SET [COLUMN] = [VALUES] 
WHEN NOT MATCHED THEN
    INSERT ([COLUMN1], [COLUMN2]) 
    VALUES(S1.[COLUMN1], S1.[COLUMN2]) 
;

MERGE INTO Basic explanation.

MERGE INTO source code.

MERGE BoardForMerge AS TAR
USING UserInfoForMerge AS SOR
  ON TAR.UserId = SOR.UserId
WHEN MATCHED THEN 
  UPDATE set UserNm = '' 
WHEN NOT MATCHED THEN
INSERT (BoardNo,BoardTitle,UserId) 
VALUES (100,'seoul',202401)
;

How to use MERGE INTO Advanced

MERGE INTO Advanced grammar.

MERGE INTO [Tarket table] AS T1 
USING (SELECT [COLUMN1], [COLUMN2] FROM [Source table]) AS S1
ON (T1.[COLUMN1] = A.[COLUMN1] 
AND T1.[COLUMN2] = A.[COLUMN2]) 
WHEN MATCHED AND condition THEN 
INSERT ([COLUMN1], [COLUMN2]) VALUES(S1.[COLUMN1], S1.[COLUMN2]) 
WHEN NOT MATCHED AND condition THEN 
UPDATE SET [COLUMN3] = S1.[COLUMN3] 
WHEN NOT MATCHED AND condition THEN 
DELETE;

MERGE INTO Advanced Description.

NOT MATCHED BY TARGET (= NOT MATCHED).

• If there is no data in TARGET TABLE, BY TARGET can be omitted because it is the same as INSERT NOT MATCHED in TARGET TABLE.

NOT MATCHED BY SOURCE.

• You can DELETE data from TARGET TABLE that does not exist in SOURCE TABLE but exists only in TARGET TABLE.
• However, a WHERE conditional statement cannot be written in a DELETE statement.

MERGE INTO Advanced Source.

MERGE INTO  BoardForMerge AS TAR
USING (SELECT UserId 
        FROM UserInfoForMerge 
     )AS SOR
ON TAR.UserId = SOR.UserId
WHEN MATCHED THEN 
  UPDATE set UserNm = '' 
WHEN NOT MATCHED AND SOR.UserId <> '' THEN
INSERT (BoardNo,BoardTitle,UserId) 
VALUES (100,'seoul',SOR.UserId) 
WHEN NOT MATCHED BY SOURCE THEN 
DELETE;