Sql server > Sql Server >> Bussiness Intelligence Views : 1230
Rate This Article :

Merge Syntax in SQL Server


The Merge Syntax in SQL Server for Insert, Update and Delete

Table of Contents

1.      Introduction

2.      Overview on Syntax

3.      Syntax functionality

4.      Conclusion

 

Introduction

 Starting with SQL Server 2008, you can use a MERGE statement 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, such as a primary key. You can then insert, modify, or delete data from the target table—all in one statement—according to how the rows match up as a result of the join.

In this article, I have provided an example that demonstrates how to work with the MERGE Syntax. I created the examples on a local instance of SQL Server 2008 R2. To try them out, you’ll need to first run the following script to create and populate the tables used in the below given examples. After completing the following steps given below you can easily understand the functionality and working of the MERGE syntax:

Overview on Syntax


The syntax basically has two sections one is the target section and the other is the source section. The syntax for the Merge will be:

Merge target using Source

On Condition

When Matched then

Update set table

When Not Matched then

Insert into table values

When not Matched by Source then

Delete;

These are the condition that involved in the merge syntax. And the target is the “Target table” which needs to be loaded, and the Source is the “Source table” from which the input values are obtained.

Eg: MERGE BookInventory bi

USING BookOrder bo

ON bi.TitleID = bo.TitleID

WHEN MATCHED AND

  bi.Quantity + bo.Quantity = 0 THEN

  DELETE

WHEN MATCHED THEN

  UPDATE

  SET bi.Quantity = bi.Quantity + bo.Quantity

WHEN NOT MATCHED BY TARGET THEN

  INSERT (TitleID, Title, Quantity)

  VALUES (bo.TitleID, bo.Title,bo.Quantity);

In this above mentioned example I have stated that my source table is BookInventory and my destination table is BookOrder. When the values present in both the tables matches with each other then the Column of the Target table(Quantity) will be Updated else new values will be inserted into the Target table.

Syntax functionality

 

For example I have created two table name book and book2, one is my source table and the other is my target table.

1.       Follow the below mentioned steps for creation of the target table. Run the below shown script for the creation of new table in the local database.

 first.jpg

 

 

2.       Similarly follow the above mentioned step for creation of the source table, using the script given below:

 Source.jpg

 

3.       Now iam inserting the values into the source and the target table. The Screen shot shown below

Values.jpg

 

·         Update Syntax using Merge

4.       Now iam updating a column in the target table using source table. When observing the values in the source and target table 3 values are getting updated. This is shown in the below screen shot

 

 Update.jpg


w.png

·         Inserting into the Target table from Source

5.       To insert into the target table from the source table, follow the below shown screen shot. Now the new 5 values are getting inserted into the target table from the source table. This is shown in the below mentioned screen shot for your reference:

 

 Insert.jpg

ww.png

 

6.       Now when to delete a record which fall into the condition, follow the below mentioned screen shot, In this I have put in a condition that if my target table’s “Quantity” Column has value 0 I need to delete that value. And as you see the below screen shot the value got deleted and as the result 6 rows updated.

 delete.jpg

delete2.jpg

 wwww.png

Conclusion

As you can see, when you use the OUTPUT clause in conjunction with the MERGE statement’s other three clauses—WHEN MATCHED, WHEN NOT MATCHED [BY TARGET], and WHEN NOT MATCHED BY SOURCE—you can perform multiple DML operations and verify your data modifications easily and efficiently. A MERGE statement can simplify your code, improve performance, and reduce your development effort. And moreover it will avoid the population of duplicate record into the table.

About Author
Venkatraman Natarajan
Total Posts 3
-
Comment this article
Name*
Email Address* (Will not be shown on this website.)
Comments*
Enter Image Text*
   
View All Comments
Ayishakhanam
Nice Post. Really Useful Information.
  Privacy   Terms Of Use   Advertise With Us   Contact Us
© 2016 Developerin.Net. All rights reserved.
Trademarks and Article Images mentioned in this site may belongs to Microsoft and other respective trademark owners.
Articles, Tutorials and all other content offered here is for educational purpose only and its author copyrights.