Using MERGE in SQL Server to insert, update and delete at the same time

By:   |   Updated: 2021-06-10   |   Comments (55)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > TSQL


In a typical Microsoft SQL Server data warehouse, quite often during the ETL cycle you need to perform INSERT, UPDATE and DELETE operations on a target table by matching the records from the source table. For example, a products dimension table has information about the products and you need to sync-up this table with the latest information about the products from the source table. You would need to write separate DML commands (INSERT statements, UPDATE statements and DELETE statements) to refresh the target table with an updated product list or perform lookups in your SQL database. Though it seems to be straight forward at first glance, it can become cumbersome when you first start using the command. Be sure performance does not degrade significantly with this approach. In this tip we will walk through how to use the MERGE statement.


Beginning with SQL Server 2008, you can use MERGE command to perform these operations in a single statement in a stored procedure or T-SQL script. This new command is similar to the UPSERT (fusion of the words UPDATE operation and INSERT operation) command of Oracle where it inserts rows that don't exist and updates the rows that do exist. With the introduction of the MERGE command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an INSERT or UPDATE or DELETE.

The MERGE operation basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. Here is the new MERGE syntax:

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
   THEN <merge_matched> ]
   THEN <merge_not_matched> ]
   THEN <merge_matched> ];

The MERGE statement basically works as separate INSERT, UPDATE, and DELETE statements all within the same statement. You specify a "Source" record set and a "Target" table and the JOIN condition between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.

How to get started with the SQL Server Merge statement?

The SQL Server MERGE command is the combination of INSERT, UPDATE and DELETE commands consolidated into a single statement. Here is how to get started with the SQL Server MERGE command:

  1. Start off by identifying the target table name which will be used in the logic.
  2. Next identify the source table name which will be used in the logic.
  3. Determine the appropriate search conditions in the ON clause in order to match rows.
  4. Specify logic when records are matched or not matched between the target and source i.e. comparison conditions.
  5. For each of these comparison conditions code the logic. When matched, generally an UPDATE condition is used. When not matched, generally an INSERT or DELETE condition is used.

Check out the example below with product data to get started down the path of becoming an expert with the SQL Server MERGE command to streamline your T-SQL logic.

SQL Server Merge Example

In this example I will take a Products table as the target table and UpdatedProducts as the source table containing an updated list of products. I will then use the MERGE command to synchronize the target table with the source table.

First let's create a target table and a source table and populate some data to these tables which can be run in SQL Server Management Studio (SSMS).

--MERGE SQL statement - Part 1

--Create a target table
   ProductName VARCHAR(100),
   Rate MONEY

--Insert records into target table
   (1, 'Tea', 10.00),
   (2, 'Coffee', 20.00),
   (3, 'Muffin', 30.00),
   (4, 'Biscuit', 40.00)

--Create source table
CREATE TABLE UpdatedProducts
   ProductName VARCHAR(100),
   Rate MONEY

--Insert records into source table
INSERT INTO UpdatedProducts
   (1, 'Tea', 10.00),
   (2, 'Coffee', 25.00),
   (3, 'Muffin', 35.00),
   (5, 'Pizza', 60.00)

SELECT * FROM Products
SELECT * FROM UpdatedProducts

Next, I will use the MERGE command to synchronize the target table with the refreshed data coming from the source table in the following example:

--MERGE SQL statement - Part 2

--Synchronize the target table with refreshed data from source table
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
--When records are matched, update the records if there is any change
--When no records are matched, insert the incoming records from source table to target table
THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target and same record does not exist in source then delete this record target
--$action specifies a column of type nvarchar(10) in the OUTPUT clause that returns 
--one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE' according to the action that was performed on that row
OUTPUT $action, 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 


When the above is run this is the output. There were 2 updates, 1 delete and 1 insert.

SQL Server Merge query results

If we select all records from the Products table, we can see the final results:

SELECT * FROM Products

We can see the Coffee rate was updated from 20.00 to 25.00, the Muffin rate was updated from 30.00 to 35.00, Biscuit was deleted and Pizza was inserted.

query results for SQL Server before Merge
query results for SQL Server after Merge

SQL Server Merge Command Key Points

  • The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise, Error 10713 is raised when a MERGE statement is executed without the statement terminator.
  • When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
  • At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However, a variable cannot be updated more than once in the same MATCHED clause.
  • Of course, it's obvious, but just to mention, the person executing the MERGE statement should have SELECT permission on the SOURCE table and INSERT, UPDATE and DELETE permissions on the TARGET table.
  • MERGE statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
  • MERGE statement takes the same kind of locks minus one Intent Shared (IS) Lock that was due to the SELECT statement in the 'IF EXISTS' as we did in previous versions of SQL Server.
  • For every INSERT, UPDATE, or DELETE action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-06-10

Comments For This Article

Monday, July 24, 2023 - 9:34:34 AM - Coverstone Back To Top (91426)
I'm going to lean in the opposite direction and veer away from MERGE. Two reasons.

First, I prefer libraries like Rhino which let's me programmatically mutate data in layers where sometimes I might need to decrypt something from a customer system to include, or use outside files, or pull from multiple source databases... the sky is the limit with this approach.

Second, SQL is probably the most expensive license your company pays for. It is far cheaper to use a plain windows box to do the heavy ETL lifting and leave SQL to just plain Insert and Update statements. Anytime you can defer SQL CPU cycles, do it!

Thursday, February 2, 2023 - 10:14:00 AM - AMIT Back To Top (90872)
Great article!
It worked really well for me in case of Insert and updates. I am wondering if we can do like

when not matched by source - I want to enddate it, Iscurrent = 0 - Is there a change in code that we can implement to do that ?

right now - if I get deleted record in source - how can we handle that ?

thanks in advance! Appreciate your help.

Wednesday, December 29, 2021 - 11:49:01 AM - Imad Back To Top (89623)
How about if that tables on different databases and difgerent servers;

Much appreciated if reply

Saturday, November 6, 2021 - 3:58:52 PM - Ron Back To Top (89417)
Very clear explanation with example.

Monday, October 4, 2021 - 10:44:30 PM - Jim Back To Top (89305)
Thanks so much for writing this very informative article. I include a citation and a link to this article in a white paper that I posted today.

Thursday, June 10, 2021 - 11:26:19 PM - Dheerendra Back To Top (88842)
Very useful article.
On the triggers point :
A merge statement triggers all the three : INSERT, UPDATE and DELETE action triggers irrespective of the commands issued on the MERGE statement.
Eg: If a MERGE statement has only INSERT and UPDATE statements, still DELETE trigger will be fired

Monday, May 24, 2021 - 9:08:23 AM - FB Back To Top (88729)
What about this?

Sunday, May 16, 2021 - 2:07:45 PM - Rams Back To Top (88689)
Thank You for given Information. here I have doubt what is difference between Merge and Trigger

Friday, October 16, 2020 - 11:05:25 AM - Nader Zouaoui Back To Top (86652)
Thanks loads for this article it helped a lot

Tuesday, July 21, 2020 - 4:56:54 PM - Greg Robidoux Back To Top (86171)

Hi Maddy,

Can you clarify what you want to do.  Do you want to run two different MERGE statements and update the same tables or have one MERGE statement run another MERGE statement?


Sunday, July 19, 2020 - 9:17:02 AM - maddy Back To Top (86154)

Can I combine two merge statements to get results in one table only?

Saturday, July 11, 2020 - 1:23:21 AM - Dilshad Mansuri Back To Top (86122)

Nice article,

I want to know that merege statement read all data of target table, bcz my target table have large data.

Sunday, December 22, 2019 - 2:52:07 AM - Asgar Back To Top (83494)

I want to deploy database using visual studio sql server data solution and setup the seed data using pre/post deployement script. The solution has two tables which has foreign key relationship. I want to add seed data/reference data to these tables. How to use T-SQL Merge statement to add data to these tables.

Also If these tables already has some data, I want to append data two these tables. Could you please suggest how to use Merge in this context. Any other approach to setup reference data to these tables.

Thank you

Monday, December 16, 2019 - 6:49:35 PM - Anand Back To Top (83442)

In when condition we are not using any brackets, how sql will understand combination of 'AND' and 'OR' .


Monday, December 2, 2019 - 12:50:10 AM - Unais T K Back To Top (83244)

Hi,can please advise below Query, May source table have more data and Target table less rows while running below query Zero rows get effect and target not getting insert.

MERGE target as t 
USING source  as s
ON (
   t.[VBELN] =s.[VBELN]
       t.[POSNR]=   s.[POSNR]
      s.[NETWR] <>t.[NETWR]OR
      s.[ARKTX] <>t.[ARKTX]OR
      s.[SPART] <>t.[SPART]OR
      t.[NETWR] =s.[NETWR],
      t.[ARKTX] =s.[ARKTX],
      t.[SPART] =s.[SPART],
         VALUES (s.[VBELN],s.[POSNR],s.[NETWR],s.[MATNR],s.[ARKTX] ,s.[SPART] ,s.[ERDAT] ,s.[WAVWR] ,s.[PRCTR],s.[AUBEL],s.[AUPOS],s.[ZKZWI6],s.[KURSK])

Thursday, September 12, 2019 - 1:29:39 AM - Bernard G Bailey Back To Top (82376)

Hi Ali,

This looks like what I need to update a sql server table from an Access 2003 (.mdb) table. 

The target table has been created in SQL Server 2008 R2


I need to insert new records into this table from an Access table called 'DocumentHeaders' on a 60 minute time schedule.  No concern about the time schedule just the code that would merge the source data with the target table.

I have found some code:

           'Driver={Microsoft Access Driver (*.mdb, *.accdb)};
            DBQ=C:\\myfile.accdb', 'SELECT * FROM [OLD]')
           'Driver={Microsoft Access Driver (*.mdb, *.accdb)};
            DBQ=C:\\myfile.accdb', 'SELECT * FROM [New]')
   ) t

Which looks like it could do the job, but I would need to untangle everything to get it to work. And that might be a stretech too far for me.

All assistance is appreciated



Friday, June 7, 2019 - 2:04:06 AM - ripunj raushan Back To Top (81360)

Thanks . This is exactly what I was looking for.

Wednesday, September 26, 2018 - 9:04:19 AM - Frank Back To Top (77730)

 Excellent post! Up to the point, clear no-nonsense presentation. A real time saver for the occasional MERGEr.

Wednesday, September 5, 2018 - 5:09:10 PM - Antony Back To Top (77370)

We have just replaced all the merge statements in our Data Warehouse with discrete update / inserts (no deletes - we have SCD's). Performance has not degraded, in some cases it is improved.

The main reason for this is reliability.

MERGE statements (as of SQL2016) do NOT work reliably with SIMPLE recovery mode. Every now and then, SQL will crash, leaving affected tables in an unstable state. The only solution is to truncate the tables.

The 'workaround' for this is to have full recovery mode enabled for the database. This is not advised for a data warehouse, as large batch updates occur every day.

A flip side is that the load queries are now simple, clean and understandable / maintainable by anyone, not just the peson that wrote them. This is actually a really important point.

Summary: MERGE queries are really clever, but offer little practical advantage in real world situations, and, currently, are not reliable & scaleable enough for use in an enterprise level scenario.

Saturday, August 11, 2018 - 10:01:53 AM - Gabriel Back To Top (77137)

Great article.

Thursday, November 23, 2017 - 6:44:50 AM - Ivan Back To Top (70125)


 I know merge join has many of issues and after so many years Microsoft hadn't fix the issue in the merge.

Although this command was attracted me from 2008.

Tuesday, August 30, 2016 - 7:46:45 AM - Muthamizhselvan P Back To Top (43217)

 Hi All,


Thanks in advance. I need to merge tables from different database having same structure. The table name will begin with "TBL_". I need to merge all the table from into a single table with a specific name in a pecified database.


Once again thanks in advance.


Friday, June 24, 2016 - 10:17:45 AM - Costin Back To Top (41757)

Absolutely awesome. Good explanations

Tuesday, April 5, 2016 - 11:29:46 AM - elsiele Back To Top (41139)

 very helpfull


Thursday, March 26, 2015 - 6:26:44 AM - lakshmana Back To Top (36706)

Hi, thank you so much this is a very nice article because i was facing a problm how to differentiate wich row is updated in final target table and i got an idea by reading your article so i declared Modified_date in target table and in mathed condition i checked if any row values is updated at the time modified_date will change and easy to identify in final result.

Friday, February 20, 2015 - 6:54:03 AM - Shmuel Milavski Back To Top (36299)

This was very helpfull to me , 

Thanks a lot!

Monday, February 9, 2015 - 11:24:58 AM - Paul Back To Top (36187)

Is there a way to show only the items that are not matched in the target table from the source table?  I run the below script in sql 2008 and get the following which is great but I want to be able to see which record did not match in the source table so I don't have to go through 120 lines of data manually.  The only table and row I am interested in is "stgitem.avgunitcost" which is the source table

use test
truncate table dbo.stgitem

insert dbo.stgitem
FROM 'C:\PriceUpdate\priceupdate.csv'

use test
merge dbo.item
using dbo.stgitem
on item.code = stgitem.code
when matched then
set item.avgunitcost = stgitem.avgunitcost,item.issuecost = stgitem.issuecost;

(120 row (s) affected)

(1 row (s) affected)

(1 row (s) affected)

(119 row (s) affected)

Friday, November 21, 2014 - 1:58:45 PM - Kimberly Ford Back To Top (35366)

This was a fabulous article. I needed to share a few tables from my data warehouse with another team but didn't want to give them access to the entire database. So, I create another database with just the tables I needed, used PART 2 and I have a fantastic method of keeping the tables in the "off database" updated! Now to just create my stored procedure and I'm set. Definitely a huge help!!! 

Friday, November 7, 2014 - 4:05:03 PM - ola Back To Top (35226)

Nice Article Ashad,

  Am currently imploring the merge function in my code, and for some reason, the matched rows are not updated in the target table when the update was done, only the Not matched rows that got inserted into the target table.

Any suggestions please.

Monday, November 3, 2014 - 12:56:05 PM - Tejas Back To Top (35168)


Nice article - very good example.

In your example above, when row does not exist in Source (WHEN NOT MATCHED BY SOURCE ) You are deleting the row from Target table.

In my case, I just want to update a flag on target that the row was marked as deleted (in Source).

SQL 2008R2 wont allow this: 


Any Suggestions?

Tuesday, September 30, 2014 - 6:52:05 PM - Irfan Back To Top (34785)

Excellent article. Thank you soo much. Works for me perfectly to optimise the code.

Wednesday, September 3, 2014 - 6:36:04 AM - sobha Back To Top (34363)

Nice article


Thanks a lot

Wednesday, June 4, 2014 - 2:34:03 AM - chotu jain Back To Top (32071)

Excellent explaination with good no of examples.

Sunday, April 27, 2014 - 6:59:33 AM - SQL2008 Back To Top (30528)

Hi Arshad, nice post and thanks for all the tips. 

I am trying to use when not matched by Target then Insert, is it possible to use a different table to insert other than the target? I want to keep the target clean and build the not matched data into a different table. someone suggested using cte, not surehot to, any suggestions are welcome, thanks


Tuesday, April 8, 2014 - 10:15:48 AM - Erhan Back To Top (30006)

Very useful, thank you!

Tuesday, December 3, 2013 - 12:57:07 PM - Sean Ed Back To Top (27675)

Thanks for the script!  It helped me design my import.


One thind I'd recommend adding for indexes is an IS NOT NULL statement for the inserts.  Even if there isn't a null, I saw an instance where the script was stopped with an error without the statement.



Monday, October 7, 2013 - 11:08:18 AM - Joan Back To Top (27061)

Thanks for this useful post.

Thursday, September 12, 2013 - 2:29:11 PM - Arshad Ali Back To Top (26772)

Yes John, I think you are right you can use either UPDATE or INSERT at one time. But that does not mean you cannot achieve what you want to. There is a trick for this.

Here is a tip which takes similar approach for managing slowly changing dimension. (it updates the matching target record and then insert the matched source record into target table along with the new records).

Please let me know if you face any issue.



Thursday, September 12, 2013 - 1:06:39 PM - John Back To Top (26771)

I do have one question regarding the MERGE command.  Can it handle an update and then an insert.  For example, if there is a match between the Target and Source then I want one field in the Target updated and then the Source record added.  How would this be done in the MERGE command?  Since SQL keeps giving the error that it does not allow INSERTS on MATCHES.



Wednesday, August 7, 2013 - 12:54:52 PM - Abraham Babu Back To Top (26136)

I do have a Update Trigger in a table. Update Trigger works fine when separate Update statement is fired. However, when MERGE statement issues a UPDATE command, the trigger is not invoked. Is there any precaution i need to make here?


Abraham Babu

Wednesday, November 14, 2012 - 1:26:58 AM - Achilles Back To Top (20330)

Thanks, solved my issue

Friday, August 24, 2012 - 12:36:58 PM - Leo Korogodski Back To Top (19215)

What if you don't have a source table? I want to insert a single row (id, value1, value2, value3, ...) if the id doesn't match or update the existing row if the id does match. Do I really have to create a one-row temporary table for this?

Monday, August 13, 2012 - 2:11:51 AM - Arshad Back To Top (19009)

Thanks Carrie Chung for your appreciation!

Friday, August 10, 2012 - 8:38:41 AM - Carrie Chung Back To Top (18990)

Good and clear example to explain the concept.

Sunday, August 5, 2012 - 9:30:37 PM - SSMS Back To Top (18922)

Can we use merge for tables from 2 different databases, for example the source table is from staging database and the targer table is in live database?

Monday, July 30, 2012 - 10:11:20 PM - Vishal Back To Top (18856)

Good 1 !!

Thursday, June 21, 2012 - 6:27:16 AM - vinod Back To Top (18147)

Nice one....

Thank you:)

Wednesday, May 30, 2012 - 6:14:47 AM - sylvia moestl vasilik Back To Top (17720)

Good solid sample code that I'll use again - especially like the output of the different actions (insert, delete, update) which I didn't realize you could do.

Friday, May 25, 2012 - 10:49:28 AM - Chandrashekar Venkatraman Back To Top (17660)

Very Good Post. Am going to use this for a project of mine.

Monday, May 14, 2012 - 5:59:56 PM - George Quiroga Back To Top (17454)

Will the MERGE work if the target table is not identical to the source table? For instance my target table only has a subset of the columns that are in the source table and it is those columns that I want updated. The columns are named the same in both tables but they are in different positions since the source table has many more columns than the destination table.



Friday, May 4, 2012 - 3:52:15 PM - joseph Jelasker Back To Top (17271)


Sorry Please avoid the previous request becaust it has some typo mistakes..Please consider this.

i have  to have 3 insert statements ,to Target Table,  inside  "Not MATCHED BLOCK" .I can't do bulk insert because the subsequence insert has to take the previously inserted Identity Column(basically primary key).. i fail to do that.can you pls provide me the solution..


Thanks in Advance,Joseph S. Jelaskar

Thursday, March 10, 2011 - 5:09:03 PM - Don Back To Top (13173)

Best example and explanation of a multi-matched/not matched merge I have encountered. Well Done!

Tuesday, September 28, 2010 - 1:46:27 PM - kv Back To Top (10212)
How do we use the merge function when the source and target are on different databases?

Wednesday, March 11, 2009 - 1:33:23 PM - arshad0384 Back To Top (2970)

First of all thanks for your appreciation and thanks again for addition, this will help the readers.

Tuesday, March 10, 2009 - 10:54:55 AM - jcelko Back To Top (2964)

Very nice job!  The only thing I would add is an example with more predicates in the WHEN clause -- 



You can get a lot of power in one statement and avoid procedural coding.  


get free sql tips
agree to terms