I attended one interview and interviewer asked me below question.
Write a single statement to insert, update and delete operation in this table.
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.
- Source Table: Main table from where changes will be moved to target table.
- 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:
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.