Create MERGE statements with data!

/*
  AUTOMATICALY CREATE MERGE STATEMENTS WITH DATA

  Author: Jason L. Selburg
  Date: 05/19/2014

  Contact: via SQLServerCentral

*/:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'   ---- this section stolen from VS created script :)
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
SET NOCOUNT ON;
/*
  this is the output file. I haven't found a reliable way to split up the files via script

  We designed a small VB application to loop through the file using the "Filename: ...sql" 
  for delimiters and split the files appropriately     */:OUT "C:\SupportingData.sql"

DECLARE 
  @i INT 
  ,@sqlTextFirst VARCHAR(MAX)
  ,@sqlTextSecond VARCHAR(MAX)
  ,@EXECText NVARCHAR(MAX)
  ,@MergeText VARCHAR(MAX)
  ,@Filename VARCHAR(256)
  ,@Debug INT = 0

DECLARE @SupportingDataTables TABLE
  ([nDex] INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
  [object_id] [int] NOT NULL,
  [schemaName] [varchar](MAX) NOT NULL,
  [tableName] [varchar](MAX) NOT NULL,
  [PK] [varchar](MAX) NULL,
  [columnlist] [varchar](MAX) NULL,
  [selectList] [varchar](MAX) NULL,
  [JoinClause] [varchar](MAX) NULL);

IF OBJECT_ID('tempdb..#DataToMerge') IS NOT NULL
  DROP TABLE #DataToMerge

CREATE TABLE #DataToMerge (nDex INT IDENTITY, UsingList NVARCHAR(MAX));

INSERT @SupportingDataTables
  SELECT DISTINCT 
    t.object_id 
    ,s.name AS schemaName
    ,t.name AS tableName
    ,icc.name AS PK
    ,STUFF((SELECT
    ',[' + c.Name + ']' AS [text()]
    FROM
    sys.COLUMNS AS c
    WHERE
    t.OBJECT_ID = c.OBJECT_ID

            ---- these are specific columns that WE ignore, you may want to remove this restriction
    AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS') 
    AND c.NAME NOT LIKE '%GUID' 
            ----
    FOR
    XML PATH('')
    ),1,1,'') AS columnlist
    ,'''' + CHAR(10) + '               ,('' + ' + STUFF((SELECT
    CASE WHEN st.precision = 0 OR
    st.user_type_id IN (40,41,42,43,8,11,241,58,61,189,98) THEN  ---- needs wrapped in quotes
    ' + '','' + ISNULL('''''''' + REPLACE([' + c.Name + '],'''''''','''''''''''') + '''''''',''NULL'')' 
    ELSE
    ' + '','' + ISNULL(REPLACE(CAST([' + c.Name + '] AS VARCHAR(MAX)),'''''''',''''''''''''),''NULL'')'
    END AS [text()]
    FROM
    sys.COLUMNS AS c
    INNER JOIN sys.types AS st
    ON c.user_type_id = st.user_type_id
    WHERE
    t.OBJECT_ID = c.OBJECT_ID

                    ---- these are specific columns that WE ignore, you may want to remove this restriction
    AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')    
    AND c.NAME NOT LIKE '%GUID'
                    -----
    FOR
    XML PATH('')
    ),1,8,'') + ' + '')''' AS selectlist
    ,STUFF(STUFF((SELECT 
            '      AND [TGT].[' + col.COLUMN_NAME + '] = ' + '[SRC].[' + col.COLUMN_NAME + ']' + CHAR(10) AS [text()]
                  FROM
                    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab
                      INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
                        ON Col.Constraint_Name = Tab.Constraint_Name
                        AND Col.Table_Name = Tab.Table_Name
                        AND Col.CONSTRAINT_SCHEMA = Tab.TABLE_SCHEMA
                  WHERE 
                    tab.CONSTRAINT_TYPE = 'PRIMARY KEY'  
                    AND tab.TABLE_SCHEMA = s.NAME
                    AND tab.TABLE_NAME = t.name          
          FOR
          XML PATH('')
    ),1,8,''),1,1,'') AS JoinClause   ---- multi column PK's ARE handled
  FROM
    sys.tables AS t
      INNER JOIN sys.schemas AS s
      ON t.schema_id = s.schema_id
      INNER JOIN sys.indexes AS i
      ON i.object_id = t.object_id
      AND i.is_primary_key = 1
      INNER JOIN sys.index_columns AS ic
      ON i.object_id = ic.object_id
      AND i.index_id = ic.index_id
      INNER JOIN sys.columns AS icc
      ON ic.column_id = icc.column_id
      AND ic.object_id = icc.object_id
      INNER JOIN sys.extended_properties AS ep  ---- marked as supporing data -- this is how we define the data that is common
        ON t.object_id = ep.major_id
        AND ep.name = 'SupportingData' 
        AND ep.value = '1'
  WHERE
    t.object_id NOT IN (SELECT object_id FROM sys.columns WHERE name = 'active' OR name  = 'isactive')
    AND 
    t.object_id NOT IN (SELECT object_id FROM sys.columns AS c WHERE system_type_id = 241)
    AND
    s.name = 'dbo'                              ---- Schema Name  ---- just another filter
    AND 
    t.name NOT IN ('CalendarDates','Numbers')   ---- we ignore numbers and calendar dates as they should be scripted individually
  ORDER BY
    t.name

SELECT @i = max(nDex) FROM @SupportingDataTables

SELECT @MergeText = NULL, @sqlTextFirst = NULL, @sqlTextSecond = NULL,@EXECText = NULL;

WHILE @i > 0
BEGIN 

  SET NOCOUNT ON;
  DELETE FROM #DataToMerge

SELECT
      @FileName = 'C:\SupportingData\' + schemaName + '.' + tableName + '.sql'
 ,@sqlTextFirst =  
            '/*' + CHAR(10) + 
            'FileName:=' + schemaName + '.' + tableName + '.sql' + CHAR(10) + 
            '*/' + CHAR(10) + CHAR(10) + 
            'MERGE INTO [' + schemaName + '].[' + tableName + '] AS [TGT]' + CHAR(10) + 
            'USING (VALUES ' 
      ,@sqlTextSecond = CHAR(10) + 
            ') AS [SRC]  ' + CHAR(10) + 
            '(' + columnList + ')' + CHAR(10) + 
            '  ON' + JoinClause + CHAR(10) +
            '---- When the record does not exist... INSERT' + CHAR(10) + CHAR(10) + 
            ' WHEN NOT MATCHED BY TARGET THEN' + CHAR(10) +  
            'INSERT ' + CHAR(10) +
            '(' + columnList + ') ' + CHAR(10) +
            'VALUES ' + CHAR(10) +
            '(' + columnList + ');' + CHAR(10) + 
            'GO' + CHAR(10) 
      ,@EXECText = CHAR(10) + 
            'SELECT ' + selectList + CHAR(10) + 
            'FROM [' + schemaName + '].[' + tableName + '] ORDER BY ' + ISNULL(PK,'1')
FROM @SupportingDataTables AS s
WHERE nDex = @i

  IF @Debug = 1
    BEGIN
      SELECT @EXECText
    END
  ELSE
    BEGIN  
      INSERT #DataToMerge
      EXEC sp_executesql @EXECText

      IF EXISTS (SELECT NULL FROM #DataToMerge AS dtm)
        BEGIN 
          SELECT
            REPLACE(@sqlTextFirst +
                          STUFF((SELECT  
                                    UsingList AS [text()]
                                  FROM 
                                    #DataToMerge
                      FOR XML PATH(''))
                              ,1,1,'')+
                      @sqlTextSecond
                ,'USING (VALUES                ,(','USING (VALUES (') 
        END 
    END 
                       
SELECT @i -= 1

END 
GO

SET NOCOUNT OFF;
GO

IF OBJECT_ID('tempdb..#DataToMerge') IS NOT NULL
  DROP TABLE #DataToMerge
GO