Merge Statement and Cross Apply

Good Morning All,

The other day I got to dive into learning about the Merge Statement within Sql Server 2008.  All I can say is wow!  This is one of my favorite new commands in SQL Server.  I think I will be using it a lot.  It makes inserts, updates, and deletes eaiser and more efficient.  In tihs blog I dont have the best explain to make use of this, but it will cover alot of different topics and hopefully help you out.

Before we can write the Merge command there are few other tricks I want I need to explain that I do in this merge statement.

I will explain the following:

1. Turn a comma list variable into a table result
2. Find the cartesian  product of two tables
3. Finally the merge statement

First I will take a comma list variable into a table result.  Here is the current variable I have.

DECLARE @Players VARCHAR(500)

SET @Players = 'Hank Aaron,Robin Yount,Ryan Braun,Corey Hart'

Now I want to output it like the following:

Player Name
Hank Aaron
Robin Yount
Ryan Braun
Corey Hart

To populate this table from the variable I use a function in my database called fn_split by using this select statement.


SELECT value AS PlayerName FROM dbo.fn_Split(@Players,',')

Here is the function defintion for you.  I beleive in giving credit where credit is due, but sadly can't give the person that wrote this function any credit.  I dont remember where I found this function, but I believe it was a forum I was searching on years ago. It has worked great for me.

CREATE FUNCTION dbo.fn_Split(@text VARCHAR(8000), @delimiter VARCHAR(20) = ', ')RETURNS @Strings TABLE
    (value VARCHAR(8000) )
            AS
    BEGIN
    DECLARE @index INT
        SET @index = -1
    WHILE (LEN(@text) > 0)
        BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0)
                    AND (LEN(@text) > 0)
            BEGIN
            INSERT INTO @Strings
                    VALUES (@text)
                        BREAK
            END
        IF (@index > 1)
            BEGIN
            INSERT INTO @Strings
                    VALUES (LEFT(@text, @index - 1))
            SET @text = RIGHT(@text, (LEN(@text) - @index))
            END
        ELSE
            SET @text = RIGHT(@text, (LEN(@text) - @index))
        END
    RETURN
    END
            

Let's set up our temp tables to work with..

--Remove table if its there
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#Teams') )DROP TABLE #Teams
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Roaster') )DROP TABLE #Roaster

--Create new table
CREATE TABLE #Teams(TeamID INT IDENTITY(10000000,1),Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))
CREATE TABLE #Roaster(TeamID INT,PlayerName VARCHAR(200),CreatedOn DATE, ModifiedOn DATETIME)



Let's create a team for our team table.

--Insert Team 
INSERT INTO  #Teams(Team,Founded,City,State) SELECT  'Milwaukee Brewers','1970','Milwaukee','WI'
  
Secondly, let's work on creatting our cartesian production between the variable and team table.  This is pretty simple with some SQL magic.  The statement makes use of the outer apply as well as the function.

SELECT * FROM #Teams
OUTER APPLY dbo.fn_Split(@Players,',')p

This is the output of the above select statement.

Finally we can create the merge statement.  It's pretty simple now that we have everything set up and ready to go.  We want to be able to insert and update values in the roaster table.

Here is the entire command at one time:

MERGE #Roaster r
USING (SELECT t.TeamID,p.value AS PlayerName 
            FROM #Teams t
            OUTER APPLY dbo.fn_Split(@Players,',')p
      ) tp ON r.TeamID=tp.TeamID 
         AND r.PlayerName=tp.PlayerName
WHEN NOT MATCHED THEN
       INSERT (teamid, PlayerName, CreatedOn)
       VALUES (tp.TeamID,tp.PlayerName,GETDATE())
WHEN MATCHED THEN  
       UPDATE SET ModifiedOn=GETDATE()
       ;

Here is the output for inserting values:

Here is the output for when values are updated:

Well that should do it for my blog entry.  Hopefully this helped you out and give you some insight on some new functions and features to use in SQL Sever.

Cheers,

Robert