Merge in SQL Server

Introduction:

I attended one interview and interviewer asked me below question.

Q 1:

Id	EName	ECode
1	Rajesh	E001
2	Suresh	E002
3	Ram	E003

Write a single statement to insert, update and delete operation in this table.

Q 2:

Id	EName	ECode
1	Rajesh	E001
2	Tiger	E002

Write a insert query to insert id = 2 with EName=”Tiger” and ECode=”E002” without using third variable to increment id or without using select query. You should use only inert query.

I was thinking how it is possible? Then he gave me hint about merge in SQL server. Hence I am writing this article to explain the merge concept.

What is merge statement?

Merge statement is introduced in SQL Server 2008. It is used to perform insert, update and delete in a single statement without writing multiple statement for insert, update and delete.

How to Use?

Two tables are required for merge.

1. Source Table: Main table from where changes will be moved to target table.
2. Target Table: The table where insert, update and delete operation will be performed.

Merge statement joins these two tables based on common column from both table.

Merge Statement Syntax:

Source Table: EmployeeSource

Target Table: EmployeeTarget

Merge statement to merge:

Merged Table:

This is final table after merge.

Points to be noted:

• Id = 1 is present in EmployeeSource and EmployeeTarget Table. So Ename and Ecode from EmployeeSource table is updated in EmployeeTarget.
• Id = 3 is not present in EmployeeSource table but not in EmployeeTarget table so it is deleted from EmployeeTarget table.
• Id=2 is present in source table but not in target table so records from EmployeeSource table is inserted into EmployeeTarget Table.

Note: Answer of both question is MERGE statement as mention above.

Please provide your valuable feedback and comments.

Thanks for reading.