What is Table-Valued Parameters and Merge Statement in SQL Server 2008?

Table-Valued Parameters and Merge Statement in SQL Server 2008  1)Table-Valued Parameters SQL Server 2008 introduces a new parameter type called the table-valued parameters.  With the table-valued parameters, which are declared by using user-defined table types as will be described below, you can now send multiple rows of data to a function or stored procedure without creating a temporary table or many parameters. To illustrate the use of table-valued parameters in comparison to how stored procedures or functions are used prior to SQL Server 2005, let's say you are maintaining a table of contacts ([dbo].[Contact]) that contains the email address, first name and last name of your contacts.  You would create a stored procedure that accepts as separate parameters the email address, first name and last name.

CREATE TABLE [dbo].[Contact] (

    [Email]         VARCHAR(100),

    [FirstName]     VARCHAR(50),

    [LastName]      VARCHAR(50)

)

GO

CREATE PROCEDURE [dbo].[usp_ProcessContact]

    @Email      VARCHAR(100),

    @FirstName  VARCHAR(50),

    @LastName   VARCHAR(50)

AS

IF NOT EXISTS (SELECT 'X' FROM [dbo].[Contact]

               WHERE [Email] = @Email)

    INSERT INTO [dbo].[Contact] ( [Email], [FirstName], [LastName] )

    VALUES ( @Email, @FirstName, @LastName )

ELSE

    UPDATE [dbo].[Contact]

    SET [FirstName] = @FirstName,

        [LastName]  = @LastName

    WHERE [Email] = @Email

GO

What the stored procedure does is insert the new contact information if the email address does not exist in the [dbo].[Contact] table.  If the email address already exists, it updates the first name and last name of the contact.  To add or update contacts, you would have to call the stored procedure as many times as you have contacts.

EXECUTE [dbo].[usp_ProcessContact] 'mickey@mouse.com', 'Mickey', 'Mouse'

EXECUTE [dbo].[usp_ProcessContact] 'minnie@mouse.com', 'Minnie', 'Mouse'

SELECT * FROM [dbo].[Contact]

Email               FirstName    LastName

------------------  -----------  -----------

mickey@mouse.com    Mickey       Mouse

minnie@mouse.com    Minnie       Mouse

The Table-Valued Parameter Way

The stored procedure above can be modified to accept just one parameter, which is the table-valued parameter, instead of 3 different parameters for the email address, first name and last name.  The first step in making use of table-valued parameters is to create a user-defined table type.  A user-defined table type is a user-defined type that represents the definition of a table structure.  To create a user-defined table type, you will use the CREATE TYPE statement, followed by the table structure definition.

CREATE TYPE [ContactTemplate] AS TABLE (

    [Email]             VARCHAR(100),

    [FirstName]         VARCHAR(50),

    [LastName]          VARCHAR(50)

)

GO

The next step after creating the user-defined table type is to use that type as the parameter of the stored procedure.  In the case of the stored procedure above, we will be replacing the 3 parameters with just one parameter, the table-valued parameter. 

CREATE PROCEDURE [dbo].[usp_ProcessContact]

    @Email      VARCHAR(100),

    @FirstName  VARCHAR(50),

    @LastName   VARCHAR(50)

The parameters of the stored procedure now becomes like this:

CREATE PROCEDURE [dbo].[usp_ProcessContact]

    @Contact    ContactTemplate READONLY

One thing to note in this parameter is the READONLY attribute.  Table-valued parameters must be passed as input READONLY parameters to stored procedures or functions.  If you forget to include the READONLY attribute, you will get the following error message:

Msg 352, Level 15, State 1, Procedure usp_ProcessContact, Line 1

The table-valued parameter "@Contact" must be declared with the READONLY option.

One thing to remember also is that DML operations such as DELETE, INSERT or UPDATE on a table-valued parameter in the body of a stored procedure or function are not allowed.  If you try to issue a DELETE, INSERT or UPDATE statement into a table-valued parameter, you will get the following error message:

Msg 10700, Level 16, State 1, Procedure usp_ProcessContact, Line 6

The table-valued parameter "@Contact" is READONLY and cannot be modified.

The body of the stored procedure will also change since we are now processing multiple rows of contacts instead of one contact at a time.  The updated stored procedure, which now uses a table-valued parameter, will now look something like the following:

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

To use this updated stored procedure, we first have to declare a local variable whose type is the user-defined table type we defined earlier and which is the type that the stored procedure is expecting.

DECLARE @Contacts ContactTemplate

Now we fill this table variable with the list of contacts we want to add or update in the [dbo].[Contact] table.

INSERT INTO @Contacts

VALUES ( 'mickey@mouse.com', 'Mickey', 'Mouse' ),

       ( 'minnie@mouse.com', 'Minnie', 'Mouse' )

The syntax of the INSERT statement shown here is the new way of inserting multiple values to a table using a single INSERT statement, which is introduced in SQL Server 2008.  For more details about this new feature, you can refer to the article entitled Multiple Value Inserts Using a Single INSERT Statement.

The last step is to pass the table variable to the stored procedure:

EXECUTE [dbo].[usp_ProcessContact] @Contacts

The complete script in calling the stored procedure that uses the table-valued parameter is as follows:

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

2)MERGE Statement One of the common tasks in database management is the maintenance of lookup tables.  Sample lookup tables are Currency Codes , Country Codes, U.S. State Codes, Product Types and so on.  Maintenance of these look-up tables are usually done using stored procedures where the normal task is to add the new record if it does not exist or update the existing record if it already exists based on the identified primary key of the table.  This involves 2 statements, issuing either an INSERT statement or an UPDATE statement depending on the existence of the record. SQL Server 2008 introduces a new statement called MERGE statement which combines these functionalities into just a single statement.  The MERGE statement performs an INSERT, UPDATE or DELETE operation on a target table based on the results of a join with source table.

To illustrate how the MERGE statement is used, let's look at the process of maintaining a table using a stored procedure that performs either an INSERT statement if the record does not exist or an UPDATE statement if the record already exists in the target table.  Prior to SQL Server 2008, your stored procedure may look like the following:

CREATE TABLE [dbo].[Employee] (

    [EmployeeNumber]        VARCHAR(10),

    [FirstName]             VARCHAR(50),

    [LastName]              VARCHAR(50),

    [Position]              VARCHAR(50)

)

GO

CREATE PROCEDURE [dbo].[usp_ProcessEmployee]

    @EmployeeNumber         VARCHAR(10),

    @FirstName              VARCHAR(50),

    @LastName               VARCHAR(50),

    @Position               VARCHAR(50)

AS

IF NOT EXISTS (SELECT 'X' FROM [dbo].[Employee]

               WHERE [EmployeeNumber] = @EmployeeNumber)

    INSERT INTO [dbo].[Employee] ( [EmployeeNumber], [FirstName], [LastName], [Position] )

    VALUES ( @EmployeeNumber, @FirstName, @LastName, @Position )

ELSE

    UPDATE [dbo].[Employee]

    SET [FirstName] = @FirstName,

        [LastName]  = @LastName,

        [Position]  = @Position

    WHERE [EmployeeNumber] = @EmployeeNumber

GO

EXECUTE [dbo].[usp_ProcessEmployee] 'ABC123', 'John', 'Smith', 'Vice President'

SELECT * FROM [dbo].[Employee]

GO

EmployeeNumber   FirstName    LastName    Position

---------------  -----------  ----------  ------------

ABC123           John         Smith       Vice President

The MERGE Statement Way

With the new MERGE statement, the stored procedure above will now look as follows:

CREATE PROCEDURE [dbo].[usp_MergeEmployee]

    @EmployeeNumber         VARCHAR(10),

    @FirstName              VARCHAR(50),

    @LastName               VARCHAR(50),

    @Position               VARCHAR(50)

AS

MERGE [dbo].[Employee] AS [Target]

USING (SELECT @EmployeeNumber, @FirstName, @LastName, @Position)

   AS [Source] ( [EmployeeNumber], [FirstName], [LastName], [Position] )

ON [Target].[EmployeeNumber] = [Source].[EmployeeNumber]

WHEN MATCHED THEN

    UPDATE SET [FirstName] = [Source][FirstName],

               [LastName]  = [Source].[LastName],

               [Position]  = [Source].[Position]

WHEN NOT MATCHED THEN

    INSERT ( [EmployeeNumber], [FirstName], [LastName], [Position] )

    VALUES ( [Source].[EmployeeNumber], [Source].[FirstName],

             [Source].[LastName], [Source].[Position] );

GO

Here are the results when calling the stored procedure containing the MERGE statement:

DELETE FROM [dbo].[Employee]

EXECUTE [dbo].[usp_MergeEmployee] 'ABC123', 'John', 'Smith', 'Vice President'

SELECT * FROM [dbo].[Employee]

GO

EmployeeNumber   FirstName    LastName    Position

---------------  -----------  ----------  ------------

ABC123           John         Smith       Vice President

EXECUTE [dbo].[usp_MergeEmployee] 'ABC123', 'John', 'Smith', 'President'

SELECT * FROM [dbo].[Employee]

GO

EmployeeNumber   FirstName    LastName    Position

---------------  -----------  ----------  ------------

ABC123           John         Smith       President

Dissecting the MERGE Statement

Now let's look at the syntax of the MERGE statement.  The basic syntax of the first part of the MERGE statement is as follows:

MERGE INTO <target_table> AS <alias>

USING <table_source>

   ON <merge_search_condition>

This syntax looks totally different from the SELECT, UPDATE, INSERT or DELETE statement that you may have been accustomed to.  The first line specifies the target table where the succeeding INSERT, UPDATE or DELETE statement will be performed.  The "AS <alias>" part of the syntax is optional.  The INTO clause also is optional.

The source table to which to get the records to merge with the target table is specified after the USING clause.  The source table can be a table or view, a function that returns a rowset, a derived table, joined tables or even the OPENXML statement.

The join condition "<merge_search_condition>" that joins the target table with the source table is specified after the ON clause.  The syntax of the MERGE statement can be compared to a SELECT statement with an INNER JOIN clause where the <target_table> is inner joined with the <table_source> using the <merge_search_condition> specified.

Extracting from the stored procedure above, the first part of the MERGE statement used is as follows:

MERGE [dbo].[Employee] AS [Target]

USING (SELECT @EmployeeNumber, @FirstName, @LastName, @Position)

   AS [Source] ( [EmployeeNumber], [FirstName], [LastName], [Position] )

ON [Target].[EmployeeNumber] = [Source].[EmployeeNumber]

In this case, the [dbo].[Employee] is the target table while [Target] is the alias associated with this table.  As for the source table, instead of using a table or view, a derived table is used using the parameters passed to the stored procedure.  The alias [Source] was assigned to this derived table followed by the column alias list corresponding to each column specified in the SELECT statement.  Make sure that the number of columns specified in the SELECT statement matches the number of columns in the column alias list.  If the number of columns in the SELECT statement is more than the number of columns in the columns list, you will get the following error message:

Msg 8158, Level 16, State 1, Line 6

'Source' has more columns than were specified in the column list.

Similarly, if the number of columns in the SELECT statement is less than the number of columns in the columns list, you will get the following error message:

Msg 8159, Level 16, State 1, Line 5

'Source' has fewer columns than were specified in the column list.

The second part of the MERGE statement tells what action will be performed for those records that match between the source table and the target table.  It also tells what action will be performed for those records that are in the source but are not in the target.  In our scenario, what we want to happen is that for those records that exist in both the target table and source table, we would want to update the data in the target table with the values in the source table.  On the other hand, for those records in the source table but are not in the target table, we would want to insert these records to the target table.  The basic syntax for the second part of the MERGE statement is as follows:

WHEN MATCHED THEN <do_either_update_or_delete>

WHEN NOT MATCHED <do_insert>

Extracting this part from the stored procedure above:

WHEN MATCHED THEN

    UPDATE SET [FirstName] = [Source][FirstName],

               [LastName]  = [Source].[LastName],

               [Position]  = [Source].[Position]

WHEN NOT MATCHED THEN

    INSERT ( [EmployeeNumber], [FirstName], [LastName], [Position] )

    VALUES ( [Source].[EmployeeNumber], [Source].[FirstName],

             [Source].[LastName], [Source].[Position] );

From this script, you can see that for those records that matched (WHEN MATCHED THEN), the [FirstName], [LastName] and [Position] columns of the target table are updated with the values from the source table.  The name of the target table is not specifed after the UPDATE statement because only the target table can be updated by the UPDATE statement.  On the other hand, for those records that are in the source table but are not in the target table (WHEN NOT MATCHED THEN), an INSERT statement is executed inserting the records from the source table into the target table for those that don't exist.

Using Row Constructors as Source

In the article Row Constructors (or Table-Valued Constructors) as Derived Table, it discusses the use of row constructors as derived tables and it shows an example of how to use it with the MERGE statement.  The stored procedure above can be modified to use row constructors as the source for the MERGE statement.  Here's how the stored procedure will look like, with the row constructor highlighted in red.

CREATE PROCEDURE [dbo].[usp_MergeEmployee]

    @EmployeeNumber                         VARCHAR(10),

    @FirstName                              VARCHAR(50),

    @LastName                               VARCHAR(50),

    @Position                               VARCHAR(50)

AS

MERGE [dbo].[Employee] AS [Target]

USING (VALUES ( @EmployeeNumber, @FirstName, @LastName, @Position ))

   AS [Source] ( [EmployeeNumber], [FirstName], [LastName], [Position] )

ON [Target].[EmployeeNumber] = [Source].[EmployeeNumber]

WHEN MATCHED THEN

    UPDATE SET [FirstName] = [Source].[FirstName],

               [LastName]  = [Source].[LastName],

               [Position]  = [Source].[Position]

WHEN NOT MATCHED THEN

    INSERT ( [EmployeeNumber], [FirstName], [LastName], [Position] )

    VALUES ( [Source].[EmployeeNumber], [Source].[FirstName],

             [Source].[LastName], [Source].[Position] );