Merge Statement With DML Operations in SQL Server

Here, you will see how to perform insert, update, and/or delete operations on a target table based on the results of a join with a source table. Using the Merge statement you can easily perform the operations insert, update, and delete logic to handle criteria for maintaining a table. The Merge statement is also used to minimize the code in comparison to Stored Procedures. The MERGE statement handles all the joining of the source and target.

The following is a sample of how to use the merge statement with source and target tables:

MERGE [INTO] <target table>
USING <source table>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>

In the preceding merge statement, there are two tables, one is the source table and the other one is the target table. You will perform updates or inserts to a target table based on a source table. 

Source Table: The source table will contain the records you either want to insert or update.

Target table: The target table will be the table where the inserts or updates will actually be performed.

Creating source table in SQL Server Database

Now create a table named UserDetail with the columns UserID, UserName and CompanyName. Set the identity property=true for UserID. The table looks as in the following:

Creating Target table in SQL Server Database

The following is the MERGE statement:

MERGE Usertable AS T

USING userdetail AS S

ON  s.UserID = T.UserID 

WHEN MATCHED THEN

  UPDATE SET T.UserName = S.UserName

WHEN NOT MATCHED THEN

  INSERT (UserName)

          VALUES (S.UserName

          )

WHEN NOT MATCHED BY SOURCE THEN

  DELETE;

 

Now using OUTPUT clause

Output Clause

 

If you have implemented an output clause for a table then:

  1. In a DELETE query we can work with only DELETED
  2. In an INSERT query we can work with only INSERTED
  3. In a UPDATE query we can work with both INSERTED as well as DELETED

The output clause allows you to hold inserted, deleted and updated values during insert, delete and update DML operations on a table in SQL Server.

To learn more about the "Output" clause:

http://www.c-sharpcorner.com/UploadFile/rohatash/output-clause-with-insert-delete-and-update-statement-in-sq/ 

The OUTPUT clause itself first specifies the built-in $action variable, that returns one of the three nvarchar(10) values INSERT, UPDATE, or DELETE. The variable is available only to the MERGE statement.

 

MERGE Usertable AS T

USING userdetail AS S

ON  s.UserID = T.UserID 

WHEN MATCHED THEN

  UPDATE SET T.UserName = S.UserName

WHEN NOT MATCHED THEN

  INSERT (UserName)

          VALUES (S.UserName

          )

WHEN NOT MATCHED BY SOURCE THEN

  DELETE

OUTPUT $action, Inserted.UserName, Inserted.UserName;