Create MERGE statements with data!

,

Let’s say you want to quickly provide a script to someone that will populate a particular table with data from your database. What if you have 200 tables? This could take some time unless you're using Red Gate's SQL Data Compare, but what if you have no access to the target database? You could certainly use the MERGE statement, but that’s a lot of code to write. What if you could run a script that created a SQL file containing all of the MERGE statements in one file and ready to run, after review of course. 

Well, here's that alternative and hopefully not just something that will help you, but get you thinking about other possibilities with script automation and SQLCMD mode.

SQLCMD mode can be enabled via the SSMS Menu Query > SQLCMDMode or Tools > Options > Query Execution and checking the "By default, open new queries in SQLCMD Mode.

Besides, what self-respecting DBA doesn’t want to know how to do it via SQL?

What data is our best candidate? Well, just about any type, lookup, setup, or static data, etc... 

We use an extended property called "SupportingData" to identify these tables. This script assumes that you have an EP on all of your supporting data tables, but this can be modified to fit your specific selection criterion. 

This script does NOT handle all situations and I'm not advertising it as a complete script. i.e. data types like Text, NTEXT, XML, Image along with those pesky FK dependencies are not addressed. I’m sure there’s a way, it’s just not in this version.

There are many possible applications here, including:

  1. As this was originally designed to be used with SSDT as a method to get our supporting data into source control. Utilizing a small home built VB app to parse the output file into individual files then including these files in your solution and setting the appropriate build action (Pre/Post deploy). POOF! Your data is now in Source Control !   
  2. You can put this into a stored procedure or function to call by a single table name, but you'll have to remove the SQLCMD parts. This would be beneficial if you need MERGES throughout the day during development.

*** This is provided as-is and with any code from the internet TEST, TEST, TEST! The goal in providing this script is to get the majority of the work done for you and for you to tweak as needed.

If you copy he output to a new query window the formatting should be retained.

Comments, suggestions and improvements are always welcome.

Enjoy!

/*
  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

Rate

4.88 (8)

Share

Share

Rate

4.88 (8)