New SQL Merge Statement In SQL server 2008
In this article I tell you about a new feature Merge Statement introduced in SQL Server 2008.
In this article I tell you about a new feature Merge Statement introduced in SQL Server 2008. Sometimes we need to perform insert, update, delete statement again and again. Coding will become lengthy for performing these operations individually. So in such type conditions, we have a new feature 'Merge Statement' introduced in SQL Server 2008.
Lets see, how to use Merge Statement
Create two tables
-- Creating Database
CREATE DATABASE Student
GO
USE Student
GO
IF ( Object_id('Employee') > 0 )
DROP TABLE Employee
GO
CREATE TABLE EmployeeDetail
(
id INT IDENTITY(1, 1),
name VARCHAR(100)
)
GO
IF ( Object_id('EmployeeDetails') > 0 )
DROP TABLE EmployeeDetails
GO
CREATE TABLE EmployeeDetails
(
id INT,
Dept varchar(20)
)
GO
insert into Employee
values(1,'EmpName_1'),
(2'EmpName_2'),
(3,'EmpName_3'),
(4,'EmpName_4'),
(5,'EmpName_5')
GO
INSERT INTO EmployeeDetails
values (1,'FINANCE'),
(2,'HR'),
(3,'Manager'),
(4,'Sales'),
(5,'Marketing')
Syntax for Merge Statement
MERGE
[TOP(expression)[PERCENT]]
[INTO]target_table[WITH(<merge_hint>)][[AS]table_alias]
USING<table_source>
ON<merge_search_condition>
[WHEN MATCHED[AND<clause_search_condition>]
THEN<merge_matched>]
[WHEN NOT MATCHED[BY TARGET][AND<clause_search_condition>]
THEN<merge_not_matched>]
[WHEN NOT MATCHED BY SOURCE[AND<clause_search_condition>]
THEN<merge_matched>]
[<output_clause>]
[OPTION(<query_hint>[ ,...n ])]
Now see, how merge statement works:
MERGE EmployeeDetails
USING Employee
ON (EmployeeDetails.id =Employee. id)
WHEN MATCHED AND EmployeeDetails.id = 2 THEN
DELETE
WHEN MATCHED THEN
UPDATE set dept =' Manager'
WHEN NOT MATCHED BY TARGET THEN
INSERT(id,dept)
VALUES(9,'Technical')
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Result will be like this:
