More MERGE Examples in SQL Server 2008

Here are a couple more examples of how to use the new MERGE statement in SQL Server 2008. The first example shows how people have used "Upserts" in the past, along with a replacement MERGE statement stored procedure. The second example shows how to MERGE between two tables.

-- More MERGE Examples

-- Example 1
-- Create table
    CREATE TABLE MyTable(pk INT PRIMARY KEY, namecol VARCHAR(10), numbercol INT);

-- Insert some data in MyTable
    INSERT INTO MyTable (pk, namecol, numbercol) Values(1, 'Glenn', 20)
    INSERT INTO MyTable (pk, namecol, numbercol) Values(2, 'Ruby', 21)
    INSERT INTO MyTable (pk, namecol, numbercol) Values(3, 'Roxy', 22)

-- Legacy_Upsert_1 SP
    CREATE PROCEDURE Legacy_Upsert_1
    @pk INT, @name VARCHAR(10), @NUMBER int
    AS

        -- Row is updated even if all the values are correct
        UPDATE MyTable 
        SET namecol = @name, numbercol = @number 
        WHERE pk = @pk
        
        --    If row did not exist, a second statement execution is required
        IF (@@ROWCOUNT = 0)
            INSERT MyTable VALUES(@pk, @name, @number)


-- Legacy_Upsert_2 SP
    CREATE PROCEDURE Legacy_Upsert_2
    @pk INT, @name VARCHAR(10), @NUMBER int
    AS

        -- Updating only if any column needs to be changed requires an extra statement
        IF EXISTS (SELECT 1 FROM MyTable WHERE pk = @pk)
            UPDATE MyTable
            SET namecol = @name, numbercol = @number
            WHERE pk = @pk AND (namecol <> @name OR numbercol <> @number)
        ELSE
            INSERT MyTable VALUES(@pk, @name, @number)



-- New Merge Upsert    
    CREATE PROCEDURE Merge_Upsert
    @pk INT, @namecol VARCHAR(20), @numbercol INT
    AS

        MERGE MyTable AS t
        USING (SELECT @pk AS pkcol, @namecol AS namecol, @numbercol AS numbercol)AS s
        ON t.pk = s.pkcol

        WHEN MATCHED
        AND (t.namecol <> s.namecol OR t.numbercol <> s.numbercol) THEN
            UPDATE SET namecol = s.namecol, numbercol = s.numbercol
        WHEN NOT MATCHED THEN
            INSERT VALUES (pkcol, namecol, numbercol);


-- See what is in the table
    SELECT * FROM dbo.MyTable

-- Try Legacy_Upsert_1 SP
    EXEC dbo.Legacy_Upsert_1  4, 'Heidi', 24

-- Try Legacy_Upsert_2 SP
    EXEC dbo.Legacy_Upsert_2  4, 'Heidi2', 24

-- See what is in the table
    SELECT * FROM dbo.MyTable

-- Try new Merge_Upsert SP
    EXEC dbo.Merge_Upsert 4, 'Heidi3', 24

-- Compare methods
    SET STATISTICS IO ON;

    EXEC dbo.Legacy_Upsert_1  4, 'Heidi3', 24
    EXEC dbo.Legacy_Upsert_2  4, 'Heidi3', 24
    EXEC dbo.Merge_Upsert 4, 'Heidi3', 24


-- Example 2
-- Create Stock and Trades tables
    CREATE TABLE Stock(Stock varchar(10) NOT NULL, Qty int NOT NULL,) ON [PRIMARY];
    ALTER TABLE Stock WITH CHECK ADD  CONSTRAINT [CK_Stock] CHECK  (([Qty]>(0)));

    CREATE TABLE Trades(Stock varchar(10) NOT NULL, Delta int NOT NULL,) ON [PRIMARY];
    ALTER TABLE Trades WITH CHECK ADD  CONSTRAINT [CK_Delta] CHECK  (([Delta]<>(0)));
    
-- Add some data to the tables
    INSERT Stock VALUES('MSFT', 10), ('BOEING', 5);
    INSERT Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);

-- Check data in both tables
    SELECT * FROM Stock
    SELECT * FROM Trades

-- Merge the data
    MERGE Stock S
    USING Trades T
    ON S.Stock = T.Stock
    WHEN MATCHED AND (Qty + Delta = 0) THEN
        DELETE -- delete stock if entirely sold
    WHEN MATCHED THEN
        -- delete takes precedence over update
        UPDATE SET Qty += Delta
    WHEN NOT MATCHED THEN
        INSERT VALUES (Stock, Delta);
        -- BOEING is deleted, GE inserted, MSFT updated

-- Check data in Stock tables
    SELECT * FROM Stock
Technorati Tags: ,

This entry was posted in SQL Server 2008. Bookmark the permalink.

28 Responses to More MERGE Examples in SQL Server 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s