Donnerstag, 22. Oktober 2009

Generate MERGE SQL-Statement

Technorati-Tags: ,

A current project of my employer deals about loading 25 flat files with the data that changed since last import. Therefore I use the MERGE SQL statement. This statement is one of my favorite features of SQL Server 2008. I don’t want live without it.

The imported and converted data will be merged into the final tables that store the data. After the first 2 tables i get bored about typing MERGE commands with 50 and more columns. The scenario is presuming that source and destination table is mostly identical.

The following SQL script generates a complete SQL MERGE statement. Just set the right parameters at the beginning of the script. After script generation customize the ON part and remove columns, that are part of your primary key.

/*
    Minimum set of parameters
*/
DECLARE
  @sourceAlias NVARCHAR(50) = 'mySource'
,@sourceTable NVARCHAR(50) = 'dbo.sourceTable'
,@destinationAlias NVARCHAR(50) = 'myDestination'
,@destinationTable NVARCHAR(50) = 'dbo.destinationTable'
/*

Nothing to change below this comment

*/

DECLARE
     @SqlString NVARCHAR(MAX)
    ,@columns4Select NVARCHAR(MAX)
    ,@columns4Insert NVARCHAR(MAX)
    ,@columns4Update NVARCHAR(MAX)
/* generate Columns for Select/Insert/Update */

-- build columns for SELECT
SELECT @columns4Select = COALESCE(@columns4Select + ',' + CHAR(10), '') + CHAR(9) + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA + '.' + TABLE_NAME = @sourceTable
ORDER BY ORDINAL_POSITION

-- build columns for INSERT
SELECT @columns4Insert = COALESCE(@columns4Insert + ',' + CHAR(10), '') + CHAR(9) + @sourceAlias + '.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA + '.' + TABLE_NAME = @sourceTable
ORDER BY ORDINAL_POSITION

-- build columns for UPDATE
SELECT @columns4Update = COALESCE(@columns4Update + ',' + CHAR(10), '') + CHAR(9) + @destinationAlias + '.' + COLUMN_NAME + ' = ' + @sourceAlias + '.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA + '.' + TABLE_NAME = @sourceTable
ORDER BY ORDINAL_POSITION
SET @SqlString = '   
MERGE ' + @destinationTable + ' AS ' + @destinationAlias + '
USING (
    SELECT
' + @columns4Select + '
    FROM ' + @sourceTable + '
    ) AS ' + @sourceAlias + '
    ON (' + @destinationAlias + '.<<Column>> = ' + @sourceAlias + '.<<Column>>)
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (
' + @columns4Select + '    
    )
    VALUES
    (
' + @columns4Insert + '
    )
WHEN MATCHED THEN
    UPDATE SET
' + @columns4Update + '   
;'

PRINT @SqlString