Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server 2012 Management Studio Keyboard Shortcuts - Document Window and Browser
SQL Server 2008

Home > SQL Server 2008 > MERGE Statement With Table-Valued Parameters
MERGE Statement With Table-Valued Parameters

In the article SQL Server 2008 - MERGE Statement, it discusses one of the new features of SQL Server 2008, the MERGE statement.  The MERGE statement performs an INSERT, UPDATE or DELETE operation on a target table based on the results of a join with a source table.

Another new feature of SQL Server 2008 is the table-valued parameter, which is discussed in the article SQL Server 2008 - Table-Valued Parameters.   With the table-valued parameters, which are declared by using user-defined table types, you can now send multiple rows of data to a function or stored procedure without creating a temporary table or many parameters.

Using Table-Valued Parameter (User-Defined Table Type) as Source Table in the MERGE Statement

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
Related Articles :