SQL 2008 MERGE Functionality

One of the new SQL 2008 T-SQL features is MERGE.

The great thing about MERGE is it allows you to perform simultaneous UPDATE, INSERT and or DELETE operations on one table. Its supported by new physical operators that combine these operations so that they can be performed in a single scan rather than multiple scans.

This means that for the first time you can assign the contents of one table or query to another in a single operation

Here’s a small example which uses a stored proc:

/********Lets create the table object*******/

IF OBJECT_ID(‘TeamMember’) IS NOT NULL

BEGIN
DROP TABLE TeamMember
END

GO

CREATE TABLE TeamMember

(

EmpId INT PRIMARY KEY IDENTITY (1,1),

FirstName VARCHAR(200),

LastName VARCHAR(200),

Department VARCHAR(200)

)

GO

/********Insert some Team Members*******/
/********here using the new insert multi row functionality available in 2008*******/

INSERT INTO TeamMember (FirstName,LastName,Department)
VALUES

(‘James’,‘Hill’,‘IT’),

(‘Sarah’,‘Parker’,‘Sales’),

(‘Nick’,‘Thompson’,‘Marketing’)

GO

/********Create a procedure that might do some Team Member admin in ‘the REAL world’ !*******/

IF OBJECT_ID(‘up_InsertTeamMember’) IS NOT NULL
BEGIN
DROP PROCEDURE up_InsertTeamMember
END

GO

CREATE PROCEDURE up_InsertTeamMember
(

@FirstName VARCHAR(200),
@LastName VARCHAR(200),
@Department VARCHAR(200),
@EmpId INT OUTPUT

)

AS

BEGIN

/********Lets do the MERGE here*******/
MERGE TeamMember
USING (SELECT @FirstName,@LastName)
tm (FirstName,LastName)
ON TeamMember.FirstName = @FirstName AND
TeamMember.LastName = @LastName

/********If its not there lets create a new Team Member*******/

WHEN NOT MATCHED THEN
INSERT (FirstName,LastName,Department)
VALUES (@FirstName,@LastName,@Department)

/********If it is there lets just set the existing id to the ouput variable*******/

WHEN MATCHED THEN
UPDATE SET @EmpId=EmpId,Department = @Department;

/********Grab the new ID*******/
SET @EmpID = COALESCE(SCOPE_IDENTITY(),@EmpID);

END

RETURN;

go

/********Heres the procedure, try adding a new memberand then a team member with the same FirstName and LastName but different department*******/

DECLARE @EmpId INT,@Message VARCHAR(100)

EXEC up_InsertTeamMember @FirstName = ‘James’,@LastName = ‘Smith’,@Department = ‘Sales’,@EmpId = @EmpId OUTPUT

PRINT @EmpId

GO

SELECT * FROM TeamMember

GO