• Compound Operators
• Multiple Value Inserts
• Row Constructor
• MERGE Statement
• Table-Valued Parameters

• Error in Importing/Exporting Geography/Geometry Columns in SQL Server 2008 Management Studio
• SQL Server 2008 to Windows Server 2008 Connection Error 1326
• Drawing a Circle with the Geometry Data Type
• Store Locator Using Geography Data Type
• Convert Latitude/Longitude to Geography Point
• MERGE Statement With Table-Valued Parameters
• SqlConnection Connection Strings
• SQL Server 2008 Date Formats

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

• MERGE Statement
• Table-Valued Parameters