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
