TABLE Type and MERGE Statement

As I get many offline questions regarding the presentation “Reducing RoundTrips” I did, thought to summarize the content we discussed as a post. Session spoke about how to reduce multiple database calls from client to database application when an updated data set (Ex. data table bound to a grid) is required to send from client application to database. In addition to that we discussed the way of handling all three operations: INSERT, UPDATE, and DELETE using a single statement. This is what the session focused on;

MERGE
MERGE statement is used to insert data that does not exist but to update if it does exist. It is an extension of UPSERT which is a known term in other database management systems for performing both UPDATE and INSERT statement using a single technique. The main benefit of this is the facility to manipulate many number of records in a single table using a source as an atomic operation. This eliminates individual statements for INSERT, UPDATE and DELETE operations, one statement which is MERGE handles all three operations. MERGE statement has following elements;

• Target: This the table or view (destination) which needs to be updated.
• Source: This contains updated data (modified, newly added) and will be used for updating the target. This can be a table, view, derived table, CTE, or table function.
• WHEN MATCHED [AND] ...... THEN: This is where the action which needs to be performed when the row in the source is found in the target, should be written. Two WHEN MATCHED clauses are allowed, limiting one for UPDATE as an action and other for DELETE as an action.
• WHEN NOT MATCHED [BY TARGET] ...... THEN: The action which performs when the row in the source is not found in the target, has to be written with this. It is usually the INSERT action.
• WHEN NOT MATCHED [BY SOURCE] ...... THEN: This is to perform an action when the row in the target is not supplied with the source. Usually it is DELETE.
• OUTPUT clause and $Action: The standard inserted and deleted virtual tables are available with MERGE. In addition to that $Action provides the operation performed; INSERT, UPDATE, DELETE. This is normally used for logging/auditing.

In order to use MERGE, a source must be defined and filled. Usually the source is defined and filled by the client but for various limitations and less facilities, we used to iterate the filled-source at the client site itself and send one row at a time to database, which makes many calls to database for updating all records in the source.

TABLE Type
This can be overcome using TABLE data type. TABLE data type can be created as a user-defined data type definition and can be used as either variable or parameter. Following are the key elements of creating a type;

• CREATE TYPE: This is used for creating the user defined type. It has to be created as a TABLE and structure has to be defined.
• READONLY keyword: If the type is used as a parameter, READONLY keyword has to be used. It is as an OUTPUT parameter is not supported.
• SqlDbType.Structured: When a data table from a .NET application is sent, parameter data type must be declared as System.Data.SqlDbType.Structured.

Here is a sample code for all;

This code creates a table called Customer and inserts 4 rows. In addition to that it creates a log table too, for recording operations.

USE tempdb

GO

-- create a table for customer

CREATE TABLE dbo.Customer

(

    CustomerId int identity(1,1) not null

    , CustomerCode char(4) not null

    , FirstName varchar(25) null

    , LastName varchar(25) not null

    , CreditLimit money not null

    , IsLocal bit not null

    , LastModified smalldatetime not null

)

GO

-- insert four records

INSERT INTO Customer

    (CustomerCode, FirstName, LastName, CreditLimit, IsLocal, LastModified)

VALUES

    ('C001', 'Dinesh', 'Priyankara', 10000, 1, getdate())

    , ('C002', 'Jane', 'K', 10000, 1, getdate())

    , ('C003', 'Martinie', 'Ku', 10000, 0, getdate())

    , ('C004', 'Joe', 'Anderson', 10000, 0, getdate())

GO

CREATE TABLE dbo.CustomerLog

(

    CustomerCode char(4) not null

    , DateModified datetime not null

    , Action varchar(15) not null

)    

GO

Here is the stored procedure for accepting an updated data set using TABLE type and updating Customer using MERGE.

-- create a stored procedure for accepting created TABLE type as a parameter

-- note that READONLY has to be used

CREATE PROC dbo.InsertCustomer @CustomerType dbo.CustomerType READONLY

AS

BEGIN

    INSERT INTO dbo.CustomerLog

    (CustomerCode, DateModified, [Action])

    SELECT o.CustomerCode, getdate(), o.[Action]

    FROM

        (MERGE INTO dbo.Customer AS c

        USING @CustomerType AS t

            ON c.CustomerCode = t.CustomerCode

        WHEN MATCHED AND t.IsLocal = 0 THEN

            UPDATE SET c.FirstName = t. FirstName

                    , c.LastName = t.LastName

                    , c.CreditLimit = t.CreditLimit

                    , c.LastModified = getdate()

        WHEN NOT MATCHED THEN

            INSERT (CustomerCode, FirstName, LastName, CreditLimit, IsLocal, LastModified)

                VALUES

                    (t.CustomerCode, t.FirstName, t.LastName, t.CreditLimit, t.IsLocal, getdate())

        WHEN NOT MATCHED BY SOURCE THEN

            DELETE

        OUTPUT isnull(inserted.CustomerCode, deleted.CustomerCode) as CustomerCode, $ACTION AS Action) AS o

END

GO

Everything is ready from database level. Here is a sample code for .NET application.

SqlConnection connection = new SqlConnection(@"Server=.\SQL2008R2;Integrated Security=SSPI;Database=tempdb");

DataTable customer = new DataTable("Customer");

customer.Columns.Add("CustomerCode", typeof(string));

customer.Columns.Add("FirstName", typeof(string));

customer.Columns.Add("LastName", typeof(string));

customer.Columns.Add("CreditLimit", typeof(decimal));

customer.Columns.Add("IsLocal", typeof(bool));

customer.Rows.Add("C001", "Dinesh", "Priyankara", 10000, true); // no change and no update

customer.Rows.Add("C002", "Jane", "Kani", 25000, true); // should not update

customer.Rows.Add("C004", "Joe", "Andrew", 35000, true); // update all

customer.Rows.Add("C005", "Kate", "Neo", 35000, true); // new record

SqlCommand command = new SqlCommand("InsertCustomer", connection);  

command.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = new SqlParameter("@CustomerType", System.Data.SqlDbType.Structured);

parameter.Value = customer;

command.Parameters.Add(parameter);

connection.Open();

command.ExecuteNonQuery();

connection.Close();

Enjoy!