Using Table-Valued Parameter (User-Defined Table Type) as Source Table in the MERGE Statement
(Available in MS SQL Server 2008 r2)
This article continues the discussion of the Table-Valued Parameters article and will combine this new feature with the MERGE statement, yet another new feature of SQL Server 2008. The stored procedure at the end of that article is as follows:
CREATE PROCEDURE [dbo].[usp_ProcessContact]
@Contact ContactTemplate READONLY
AS
-- Update First Name and Last Name for Existing Emails
UPDATE A
SET [FirstName] = B.[FirstName],
[LastName] = B.[LastName]
FROM [dbo].[Contact] A INNER JOIN @Contact B
ON A.[Email] = B.[Email]
-- Add New Email Addresses
INSERT INTO [dbo].[Contact] ( [Email], [FirstName], [LastName] )
SELECT [Email], [FirstName], [LastName]
FROM @Contact A
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Contact] B
WHERE A.[Email] = B.[Email])
GO
The definition of the ContactTemplate user-defined table type is as follows:
CREATE TYPE [ContactTemplate] AS TABLE (
[Email] VARCHAR(100),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
GO
As can be seen from this stored procedure, what it does is first update the FirstName and LastName in the [dbo].[Contact] table for all those contacts that exist in the @Contact table-valued parameter based on the Email address. Then it adds those contact information that are in the @Contact table-valued parameter but are not yet in the [dbo].[Contact] table.
Converting to the MERGE Statement
The first step in converting the UPDATE and INSERT statements into the MERGE statement is to identify the source table and the destination or target table. In this case the source table is the @Contact user-defined table variable and the destination or target table is the [dbo].[Contact] table. Once the tables have been identified, we then need to determine the columns that will be used to "merge" the two tables. In our case, this will be the [Email] column of the [dbo].[Contact] table joined with the [Email] column of the @Contact user-defined table variable.
We then use these tables in the MERGE statement and join them on the columns identified, which will look as follows:
MERGE [dbo].[Contact] AS [Target]
USING @Contact AS [Source]
ON [Target].[Email] = [Source].[Email]
The next step after identifying the tables to "merge" and the columns to join these tables with, we now need to determine what to do for those records that exist on both tables based on the joined column. In our case, we would want to update the [FirstName] and [LastName] in the [dbo].[Contact] target table with the values in the @Contact source table.
WHEN MATCHED THEN
UPDATE SET [FirstName] = [Source].[FirstName],
[LastName] = [Source].[LastName]
Since the target table has already been identified in the MERGE statement, there's no need to include the target table in the UPDATE statement. If the target table is included in the UPDATE statement, you will get the following error message:
WHEN MATCHED THEN
UPDATE [dbo].[Contact]
SET [FirstName] = [Source].[FirstName],
[LastName] = [Source].[LastName]
Msg 102, Level 15, State 1, Procedure usp_ProcessContact, Line 11
Incorrect syntax near 'dbo'.
After specifying the statement to perform for those records that exist on both the source table and the target table, the next step is to determine what to do for those records that exists in the source table but are not in the target table. These records are the new contacts that are not yet in the [dbo].[Contact] table. In our case, we would want to add those new contacts to the [dbo].[Contact] destination table. The INSERT statement that will be included in the MERGE statement will look like this:
WHEN NOT MATCHED THEN
INSERT ( [Email], [FirstName], [LastName] )
VALUES ( [Source].[Email], [Source].[FirstName], [Source].[LastName] );
Just like in the UPDATE statement above, since the target table has already been specified in the MERGE statement, there's no need to include the target table in the INSERT statement. If the target table is included in the INSERT statement, you will get the following error message:
WHEN NOT MATCHED THEN
INSERT INTO [dbo].[Contact] ( [Email], [FirstName], [LastName] )
VALUES ( [Source].[Email], [Source].[FirstName], [Source].[LastName] );
Msg 156, Level 15, State 1, Procedure usp_ProcessContact, Line 14
Incorrect syntax near the keyword 'INTO'.
Putting It All Together
Converting the UPDATE and INSERT statements into a single MERGE sstatement, the stored procedure will now look like this:
CREATE PROCEDURE [dbo].[usp_ProcessContact]
@Contact ContactTemplate READONLY
AS
MERGE [dbo].[Contact] AS [Target]
USING @Contact AS [Source]
ON [Target].[Email] = [Source].[Email]
WHEN MATCHED THEN
UPDATE SET [FirstName] = [Source].[FirstName],
[LastName] = [Source].[LastName]
WHEN NOT MATCHED THEN
INSERT ( [Email], [FirstName], [LastName] )
VALUES ( [Source].[Email], [Source].[FirstName], [Source].[LastName] );
GO
To use this stored procedure, you simply declare a local variable with a type of ContactTemplate, fill up the variable with data and pass it as a table-valued parameter to the stored procedure.
DECLARE @Contacts ContactTemplate
INSERT INTO @Contacts
VALUES ( 'mickey@mouse.com', 'Mickey', 'Mouse' ),
( 'minnie@mouse.com', 'Minnie', 'Mouse' )
EXECUTE [dbo].[usp_ProcessContact] @Contacts
SELECT * FROM [dbo].[Contact]
Email FirstName LastName
------------------ ----------- -----------
mickey@mouse.com Mickey Mouse
minnie@mouse.com Minnie Mouse
