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.
  • 1815

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:

 MergeStatement.jpg

© 2019 DotNetHeaven. All rights reserved.