Generate MERGE SQL-Statement

Technorati-Tags: SQL,MERGE

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

How to query for table without primary key

At my daily work I need to audit my tables for missing primary keys. This query uses the standardized “information_schema” schema, so it should probably work with other databases than MS SQL Server 2008.

SELECT
  t.table_catalog
, t.table_schema
, t.table_name
, t.TABLE_TYPE
FROM information_schema.tables t
LEFT JOIN
    (
        SELECT
              table_catalog
            , table_schema
            , table_name
        FROM information_schema.table_constraints
        WHERE constraint_type = 'PRIMARY KEY'
    ) SubQuery ON
    t.TABLE_CATALOG = SubQuery.TABLE_CATALOG AND
    t.TABLE_NAME = SubQuery.TABLE_NAME AND
    t.TABLE_SCHEMA = SubQuery.TABLE_SCHEMA
WHERE SubQuery.TABLE_CATALOG IS NULL AND t.TABLE_TYPE = 'BASE TABLE';

 

Technorati-Tags: SQL,MS SQL Server