Skip to content
Home ยป SQL MERGE

SQL MERGE

SQL Server MERGE statement is used to modify data in a target table based on data in a source table.





The statement joins the target to the source by using a column common to both tables.

Using Merge statement you can insert, update and delete the data from the target table through only single statement – according to how the rows match up as a result of the join.

Syntax

MERGE target_table USING source_tableโ€ฉON conditionโ€ฉWHEN MATCHEDโ€ฉ    THEN update_statementโ€ฉWHEN NOT MATCHED BY TARGETโ€ฉ    THEN insert_statementโ€ฉWHEN NOT MATCHED BY SOURCEโ€ฉ    THEN DELETE;

MERGE AND USING – Source and Target table are specified to merge statement by Using keyword.

CONDITION – Source table are matched to the rows from the target table. It is similar to the join condition in the join clause.

Condition could have three possible result set as given below.

MATCHED: When rows match the merge condition. For the matching rows, It updates the rows columns in the target table with values from the source table.

NOT MATCHED BY TARGET: When the rows from the source table do not have any matching rows in the target table. In this case, It adds the rows from the source table to the target table.

NOT MATCHED BY SOURCE : When the rows in the target table do not match any rows in the source table. If you want to validate the data in target table with the data in the source table, then you use this not match condition to delete rows from the target table.

Lets look at an example of using Merge statement in SQL Server.

Suppose, you got a task where you have to validate the data between two tables tables one is source and other is target table based on following conditions:

Update the records: If target table has matching rows with source table then update the rows in the target table with the values coming from the source table.

Insert records: If source table does not have matching rows with target table then insert rows which are in the source table into the target table.

Delete records: if target table has any row/ rows which does not exist in the source table then delete those rows from target table to maintain the data integrity between source and target table.

To implement the SQL logic for such conditions, definitely you need to write three different SQL statement to perform insert, update, and delete operations based on matching values between source and target table.

However, you can do it using a single statement that is SQL MERGE statement rather than writing three separate T-SQL statement.

Lets try to understand this with the help of following example, first we create a two sample tables named EMP_SRC ( a source table) and EMP_TRGT ( a target table) and insert some records.

CREATE TABLE EMP_SRCโ€ฉ(ID INT, โ€ฉNAME VARCHAR(30), โ€ฉCODE VARCHAR(10))โ€ฉโ€ฉCREATE TABLE EMP_TRGTโ€ฉ(ID INT, โ€ฉNAME VARCHAR(30), โ€ฉCODE VARCHAR(10))โ€ฉINSERT INTO  EMP_SRC(ID, NAME, CODE)โ€ฉVALUES(1, 'A', 'A12'),โ€ฉ(2, 'B', 'B12'),โ€ฉ(3, 'C','C12'),โ€ฉ(4, 'D','D12')โ€ฉโ€ฉINSERT INTO  EMP_TRGT(ID, NAME, CODE)โ€ฉVALUES(1, 'D', NULL),โ€ฉ(2, 'Y', 'Y12'),โ€ฉ(5, 'G','G12'),โ€ฉ(6, 'E','E12')





Here we have a source table (EMP_SRC) and a target table (EMP_TRGT) as given below .

As you can see, a source and target table both contains some records and the ID column is the common column in both table which will be used to merge condition.

For ID=1 and 2, The rows from source table EMP_SRC matches with the rows from the target table EMP_TRGT, so the merge statement updates the values for the NAME, and CODE columns in the EMP_TRGT table.

For ID =3 and 4, The rows from the source table EMP_SRC does not exist in the target table EMP_TRGT, so the merge statement insert rows for these IDs into the target table.

For ID = 5 and 6, The rows from the Target table EMP_TRGT does not exist in source table EMP_SRC, so the merge statement delete these rows from target table EMP_TRGT.

You can also refer the below diagram to understand the MERGE statement flow.

 MERGE EMP_TRGT  AS TRGT โ€ฉ   USING EMP_SRC AS SRC ON SRC.ID = TRGT.ID โ€ฉWHEN MATCHED     โ€ฉ   THEN UPDATE SET TRGT.NAME = SRC.NAME, TRGT.CODE =  SRC.CODE โ€ฉWHEN NOT MATCHED BY TARGET     โ€ฉ   THEN INSERT (ID, NAME, CODE) VALUES (SRC.ID, SRC.NAME, SRC.CODE) โ€ฉWHEN NOT MATCHED BY SOURCE     โ€ฉ   THEN DELETE;

Now you can check EMP_SRC and EMP_TRGT table records.

SELECT * FROM EMP_SRCโ€ฉSELECT * FROM EMP_TRGT

As you can see, Merge statement has made the changes in target table EMP_TRGT based on matching values in source table EMP_SRC.




You can also capture the data for records that are inserted, updated, and deleted using merge statement, it can be done using OUTPUT clause.

The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE or DELETE statements,

The OUTPUT clause gives access to two virtual tables that are called as Magic Tables. These are

  • INSERTED contains the new rows (INSERT or UPDATEโ€˜s SET)
  • DELETED contains the old copy of the rows(UPDATEโ€˜s SET)

So, using OUTPUT clause with Merge statement, you can see the inserted, updated, and deleted records as a result.

MERGE EMP_TRGT  AS TRGT โ€ฉ  USING EMP_SRC AS SRC ON SRC.ID = TRGT.ID โ€ฉWHEN MATCHED     โ€ฉ  THEN UPDATE SET TRGT.NAME = SRC.NAME, TRGT.CODE =  SRC.CODE โ€ฉWHEN NOT MATCHED BY TARGET     โ€ฉ  THEN INSERT (ID,NAME, CODE) VALUES (SRC.ID, SRC.NAME, SRC.CODE) โ€ฉWHEN NOT MATCHED BY SOURCE     โ€ฉ  THEN DELETE โ€ฉOUTPUTโ€ฉ  $action,โ€ฉ  deleted.*,โ€ฉ  inserted.*;

Also Read..

SQL Server Stored Procedure

Encrypt SQL Stored Procedure

SQL Server Views

SQL Server Output Clause




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 4,357 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.