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:

image

Source Table: EmployeeSource

image

Target Table: EmployeeTarget

image

Merge statement to merge:

image


Merged Table:

This is final table after merge.

image

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.Smile

Be the first to comment

Leave a Reply

Your email address will not be published.


*