Skip to content

Instantly share code, notes, and snippets.

@ryanvs

ryanvs/Create-Merge-Script.sql

Last active September 21, 2018 19:36
Show Gist options
  • Save ryanvs/62138cb087881d80c657bdfedacd58cf to your computer and use it in GitHub Desktop.
Save ryanvs/62138cb087881d80c657bdfedacd58cf to your computer and use it in GitHub Desktop.
Download ZIP
Create SQL Merge using XML
Raw
Create-Merge-Script.sql
-- *************************************************************************************************************************
-- Name - Create-Merge-Script.sql
-- Description - Generates a SQL script to manage table data using XML with MERGE.
-- - Change variable @table to the desired output table name, and add @ignore_columns as necessary
-- - The output will be SQL lines that can be saved as a script
-- Author - Ryan van Slooten
----------------------------------------------------------------------------------------------------------------------------
-- Change Date Change By Ver. Changes
----------------------------------------------------------------------------------------------------------------------------
-- 2017-02-27 rjvs 1.0.4 Added comment block
-- 2017-10-19 rjvs 1.0.5 Used a temporary table for xml loading and comparison
-- 2018-09-21 rjvs 1.0.6 Added CAST for source/target columns
-- *************************************************************************************************************************
SET NOCOUNT ON
DECLARE @table nvarchar(100)
DECLARE @ignore_columns TABLE (name nvarchar(40))
-- ====================================
-- PARAMETER: Name of table to merge (Assumes schema/owner)
-- ====================================
SET @table = 'MES_sample_attr_ext'
-- ====================================
-- PARAMETER: Add any columns to ignore
-- ====================================
INSERT @ignore_columns VALUES ('last_edit_at'), ('last_edit_by')
-- ============================================================================
DECLARE @temp_table nvarchar(100)
, @merge_table nvarchar(100)
, @tran_name nvarchar(100)
, @max_len int = 0
, @has_identity bit = 0;
SET @temp_table = '@temp_' + @table
SET @merge_table = QUOTENAME('#merge_' + @table)
SET @tran_name = QUOTENAME('tran_' + @table)
DECLARE @lines TABLE (id int IDENTITY(1,1), line nvarchar(max))
DECLARE @base_columns TABLE (id int IDENTITY(1,1), name nvarchar(80), quote_name nvarchar(80), data_type nvarchar(40), orig_data_type nvarchar(40), position int, is_pk bit DEFAULT 0, is_identity bit DEFAULT 0, cast_source bit DEFAULT 0, cast_target bit DEFAULT 0)
DECLARE @merge_columns TABLE (id int IDENTITY(1,1), name nvarchar(80), data_type nvarchar(40), orig_data_type nvarchar(40))
DECLARE @pk_columns TABLE (id int IDENTITY(1,1), name nvarchar(80))
INSERT @base_columns (name, quote_name, orig_data_type, data_type, cast_source, cast_target, position)
SELECT col.COLUMN_NAME
-- Quoted name (if required)
, CASE
WHEN col.COLUMN_NAME = SUBSTRING(QUOTENAME(col.COLUMN_NAME), 2, LEN(col.COLUMN_NAME)) THEN
col.COLUMN_NAME
ELSE
QUOTENAME(col.COLUMN_NAME)
END
-- Original data type
, CASE col.DATA_TYPE
WHEN 'nvarchar' THEN
col.DATA_TYPE + '(' + IIF(col.CHARACTER_MAXIMUM_LENGTH = -1, 'max', CAST(col.CHARACTER_MAXIMUM_LENGTH AS nvarchar)) + ')'
ELSE
col.DATA_TYPE
END
-- Mapped data type
, CASE col.DATA_TYPE
WHEN 'text' THEN
'varchar(max)'
WHEN 'varchar' THEN
col.DATA_TYPE + '(' + IIF(col.CHARACTER_MAXIMUM_LENGTH = -1, 'max', CAST(col.CHARACTER_MAXIMUM_LENGTH AS nvarchar)) + ')'
WHEN 'ntext' THEN
'nvarchar(max)'
WHEN 'nvarchar' THEN
col.DATA_TYPE + '(' + IIF(col.CHARACTER_MAXIMUM_LENGTH = -1, 'max', CAST(col.CHARACTER_MAXIMUM_LENGTH AS nvarchar)) + ')'
ELSE
col.DATA_TYPE
END
-- Is CAST required for SOURCE column comparison
, CASE
WHEN col.DATA_TYPE IN ('text', 'ntext', 'binary', 'varbinary', 'xml') THEN
1
ELSE
0
END
-- Is CAST required for TARGET column comparison
, CASE
WHEN col.DATA_TYPE IN ('binary', 'varbinary', 'xml') THEN
1
ELSE
0
END
, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS AS col
WHERE col.TABLE_NAME = @table
AND col.COLUMN_NAME NOT IN (SELECT name FROM @ignore_columns)
ORDER BY ORDINAL_POSITION
INSERT @merge_columns (name, data_type)
VALUES ('[action]', 'nvarchar(10)')
INSERT @merge_columns (name, data_type)
SELECT name, data_type
FROM (
SELECT QUOTENAME('old_' + name) AS name
, data_type
, position
FROM @base_columns
UNION
SELECT QUOTENAME('new_' + name) AS name
, data_type
, position
FROM @base_columns
) x
ORDER BY position, name DESC
-- Find the maximum column name length
SELECT @max_len = COALESCE(MAX(LEN(name)), 0) + 1
FROM @merge_columns
-- Set the maximum length as a multiple of 4 for indentation
SET @max_len = CONVERT(int, @max_len / 4) * 4;
IF @max_len = 0 SET @max_len = 4;
-- Find the primary key(s)
INSERT @pk_columns
SELECT bc.quote_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk ON (tc.TABLE_NAME = pk.TABLE_NAME AND tc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
JOIN @base_columns bc ON (bc.name = pk.COLUMN_NAME)
WHERE pk.TABLE_NAME = @table
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
UPDATE bc
SET is_pk = 1
FROM @base_columns bc
JOIN @pk_columns pk ON (bc.name = pk.name)
-- Check if table has an identity
UPDATE bc
SET is_identity = 1
FROM @base_columns bc
JOIN INFORMATION_SCHEMA.COLUMNS co ON (bc.name = co.COLUMN_NAME AND co.TABLE_NAME = @table)
WHERE COLUMNPROPERTY(OBJECT_ID(co.TABLE_NAME), co.COLUMN_NAME, 'IsIdentity') = 1
IF (SELECT COUNT(*) FROM @base_columns WHERE is_identity = 1) > 0
SET @has_identity = 1
-- ===================================================================
-- Debugging
--SELECT * FROM @base_columns
--SELECT * FROM @merge_columns
-- ===================================================================
INSERT @lines VALUES
('DECLARE @script_action int = 0 -- 0=Select differences, -1=Rollback, 1=Commit')
, (' , @delete_target bit = 0 -- !!BE CAREFUL!!')
, ('')
, ('/* ==== To dump XML data ====')
, ('SELECT')
INSERT @lines
SELECT ' '
+ IIF(id = 1, ' ', ', ')
+ QUOTENAME(name)
FROM @base_columns
ORDER BY position;
INSERT @lines VALUES
('FROM ' + QUOTENAME(@table) + ' AS ' + QUOTENAME(@table))
, ('--WHERE TODO: Add conditions')
, ('FOR XML AUTO, ROOT(''xml'')')
, ('==== To dump XML data ==== */')
, ('')
, ('-- Internal variables')
, ('DECLARE @identity_insert bit = 0 -- IDENTITY_INSERT flag')
, (' , @text nvarchar(max) -- For OPENROWSET to bulk load XML file')
, (' , @xml xml; -- Converting text to xml')
, ('')
, ('BEGIN TRY')
, ('')
, (' SET @text = (')
, (' SELECT *')
, (' FROM OPENROWSET(')
, (' BULK ''C:\Data\' + @table + '.xml'', SINGLE_BLOB')
, (' ) AS x')
, (' );')
, (' SET @xml = CONVERT(xml, @text)')
, ('')
, (' --SELECT @text AS text, @xml AS xml')
, ('')
, (' --EXEC sp_xml_preparedocument @idoc OUTPUT, @text;')
, ('')
-- ===================================================================
, (' IF OBJECT_ID(''tempdb..' + @merge_table + ''') IS NOT NULL')
, (' DROP TABLE ' + @merge_table)
, ('')
, (' CREATE TABLE ' + @merge_table + ' (')
;
INSERT @lines
SELECT ' '
+ CASE id
WHEN 1 THEN ' '
ELSE ', '
END
+ name
+ REPLICATE(' ', @max_len + 4 - LEN(name))
+ data_type
-- + REPLICATE(' ', 25 - LEN(data_type))
FROM @merge_columns
INSERT @lines VALUES
(' );')
, ('')
INSERT @lines VALUES
(' DECLARE ' + @temp_table + ' TABLE (')
INSERT @lines
SELECT ' '
+ IIF(id = 1, ' ', ', ')
+ QUOTENAME(name)
+ REPLICATE(' ', @max_len + 4 - LEN(name))
+ data_type
FROM @base_columns
ORDER BY position;
INSERT @lines VALUES
(' );')
, ('')
-- =================
-- XML using NODES
INSERT @lines VALUES
(' INSERT ' + @temp_table + ' (')
INSERT @lines
SELECT ' '
+ IIF(id = 1, ' ', ', ')
+ QUOTENAME(name)
FROM @base_columns
ORDER BY position;
INSERT @lines VALUES
(' )')
, ('/* ==== Using XML nodes() ==== */')
INSERT @lines
SELECT CONCAT(
' '
, CASE id
WHEN 1 THEN 'SELECT '
ELSE ' , '
END
, CASE data_type
WHEN 'xml' THEN
'x.a.query(''' + name +'/*'')'
+ REPLICATE(' ', @max_len + 28 - LEN(name))
ELSE
'x.a.value(''@'
+ name
+ '''' + REPLICATE(' ', @max_len - LEN(name))
+ ', '''
+ data_type
+ ''')' + REPLICATE(' ', 25 - LEN(data_type))
END
, ' AS [' + name + ']'
)
FROM @base_columns
ORDER BY position;
INSERT @lines VALUES
(' FROM @xml.nodes(''/xml/' + @table + ''') AS x(a)')
, ('/* ==== */')
-- =================
-- OPENXML
INSERT @lines VALUES
('/* ==== Using OPENXML ====')
, (' SELECT *')
, (' FROM OPENXML(@idoc, ''/xml/' + @table + ''', 1)')
, (' WITH (');
INSERT @lines
SELECT ' '
+ CASE id
WHEN 1 THEN ' '
ELSE ', '
END
+ name
+ REPLICATE(' ', @max_len - LEN(name))
+ data_type
+ REPLICATE(' ', 25 - LEN(data_type))
+ '''@' + name + ''''
FROM @base_columns;
INSERT @lines VALUES
(' )')
, ('==== */')
INSERT @lines VALUES
('')
, (' IF COALESCE(@script_action, 0) <> 0')
, (' BEGIN')
, (' BEGIN TRANSACTION ' + @tran_name + ';')
, ('');
IF @has_identity <> 0
BEGIN
INSERT @lines VALUES
(' SET IDENTITY_INSERT ' + QUOTENAME(@table) + ' ON;')
, (' SET @identity_insert = 1;');
END;
SELECT @max_len = COALESCE(MAX(LEN(name)), 0) + 1
FROM @base_columns
INSERT @lines VALUES
('')
, (' MERGE ' + QUOTENAME(@table) + ' AS target')
, (' USING ' + @temp_table + ' AS source')
, (' ON (')
INSERT @lines
SELECT ' '
+ CASE id
WHEN 1 THEN ' '
ELSE ' AND '
END
+ 'source.' + QUOTENAME(name)
+ ' = '
+ 'target.' + QUOTENAME(name)
FROM @pk_columns
INSERT @lines VALUES
(' )')
, (' WHEN MATCHED AND (')
INSERT @lines
SELECT CONCAT(
' '
, CASE ROW_NUMBER() OVER(ORDER BY id)
WHEN 1 THEN ' '
ELSE ' OR '
END
, 'NULLIF('
, compare_source
, REPLICATE(' ', @max_len - LEN(name))
, ', '
, compare_target
, REPLICATE(' ', @max_len - LEN(name))
, ') IS NOT NULL OR NULLIF('
, compare_target
, REPLICATE(' ', @max_len - LEN(name))
, ', '
, compare_source
, REPLICATE(' ', @max_len - LEN(name))
, ') IS NOT NULL'
)
FROM (
SELECT b.*
, IIF(b.cast_source = 1, CONCAT('CAST(source.', b.quote_name, ' AS nvarchar(max))'), CONCAT('source.', b.quote_name)) AS compare_source
, IIF(b.cast_target = 1, CONCAT('CAST(target.', b.quote_name, ' AS nvarchar(max))'), CONCAT('target.', b.quote_name)) AS compare_target
FROM @base_columns b
) AS b
WHERE is_pk = 0
AND name NOT IN (SELECT name FROM @ignore_columns);
INSERT @lines VALUES
(' ) THEN')
, (' UPDATE SET')
INSERT @lines
SELECT ' '
+ CASE ROW_NUMBER() OVER(ORDER BY id)
WHEN 1 THEN ' '
ELSE ' , '
END
+ quote_name
+ REPLICATE(' ', @max_len - LEN(quote_name))
+ '= source.'
+ quote_name
FROM @base_columns
WHERE is_pk = 0
AND is_identity = 0;
INSERT @lines VALUES
(' WHEN NOT MATCHED BY TARGET THEN')
, (' INSERT (')
INSERT @lines
SELECT ' '
+ CASE id
WHEN 1 THEN ' '
ELSE ' , '
END
+ quote_name
FROM @base_columns;
INSERT @lines VALUES
(' ) VALUES (')
INSERT @lines
SELECT ' '
+ CASE id
WHEN 1 THEN ' '
ELSE ' , '
END
+ 'source.'
+ quote_name
FROM @base_columns
INSERT @lines VALUES
(' )')
, (' WHEN NOT MATCHED BY SOURCE AND @delete_target = 1 THEN')
, (' DELETE')
-- =================
-- MERGE OUTPUT clause
INSERT @lines VALUES
(' OUTPUT $action')
INSERT @lines
SELECT ' '
+ ', deleted.' + quote_name
+ REPLICATE(' ', @max_len - LEN(quote_name))
+ ', inserted.' + quote_name
FROM @base_columns
INSERT @lines VALUES
(' INTO ' + @merge_table + ';')
, ('')
, (' SELECT * FROM ' + @merge_table + ' ' + @table + ' FOR XML AUTO, ROOT(''change_log'');')
, ('');
INSERT @lines VALUES
(' IF COALESCE(@script_action, 0) > 0')
, (' BEGIN')
, (' COMMIT TRANSACTION ' + @tran_name + ';')
, (' PRINT ''COMMIT changes to ' + @table + ''';')
, (' END')
, (' ELSE')
, (' BEGIN')
, (' ROLLBACK TRANSACTION ' + @tran_name + ';')
, (' PRINT ''ROLLBACK changes to ' + @table + ''';')
, (' END')
, (' END')
, (' ELSE')
, (' BEGIN')
, (' -- Show differences')
, (' SELECT *')
, (' FROM ' + QUOTENAME(@table) + ' AS target')
, (' FULL OUTER JOIN ' + @temp_table + ' AS source')
, (' ON (')
INSERT @lines
SELECT CONCAT(
' '
, CASE id
WHEN 1 THEN ' '
ELSE ' AND '
END
, 'source.', name
, ' = '
, 'target.', name
)
FROM @pk_columns
INSERT @lines VALUES
(' )')
, (' WHERE')
INSERT @lines
SELECT CONCAT(
' '
, CASE id
WHEN 1 THEN ' '
ELSE ' OR '
END
, 'source.', name, ' IS NULL'
, ' OR target.', name, ' IS NULL'
)
FROM @pk_columns
INSERT @lines
SELECT CONCAT(
' OR '
, 'NULLIF('
, compare_source
, REPLICATE(' ', @max_len - LEN(name))
, ', '
, compare_target
, REPLICATE(' ', @max_len - LEN(name))
, ') IS NOT NULL OR NULLIF('
, compare_target
, REPLICATE(' ', @max_len - LEN(name))
, ', '
, compare_source
, REPLICATE(' ', @max_len - LEN(name))
, ') IS NOT NULL'
)
FROM (
SELECT b.*
, IIF(b.cast_source = 1, CONCAT('CAST(source.', b.quote_name, ' AS nvarchar(max))'), CONCAT('source.', b.quote_name)) AS compare_source
, IIF(b.cast_target = 1, CONCAT('CAST(target.', b.quote_name, ' AS nvarchar(max))'), CONCAT('target.', b.quote_name)) AS compare_target
FROM @base_columns b
) AS b
WHERE is_pk = 0
AND name NOT IN (SELECT name FROM @ignore_columns);
INSERT @lines VALUES
(' END')
, ('')
, ('END TRY')
, ('BEGIN CATCH')
, ('')
, (' DECLARE @ERROR_NUMBER AS int = ERROR_NUMBER()')
, (' , @ERROR_SEVERITY AS int = ERROR_SEVERITY()')
, (' , @ERROR_STATE AS int = ERROR_STATE()')
, (' , @ERROR_LINE AS int = ERROR_LINE()')
, (' , @ERROR_MESSAGE AS nvarchar(max) = ERROR_MESSAGE()')
, (' , @ERROR_PROCEDURE AS nvarchar(256) = ISNULL(ERROR_PROCEDURE(), ''-'')')
, ('')
, (' PRINT ''ERROR '' + CONVERT(nvarchar(10), @ERROR_NUMBER) + '' - '' + @ERROR_MESSAGE;')
, ('')
, (' PRINT ''CATCH - ROLLBACK changes to ' + @table + ''';')
, (' ROLLBACK TRANSACTION ' + @tran_name + ';')
, ('')
, (' -- Clean up')
, (' --EXEC sp_xml_removedocument @idoc;')
, ('')
, (' SELECT @ERROR_NUMBER AS ERROR_NUMBER')
, (' , @ERROR_SEVERITY AS ERROR_SEVERITY')
, (' , @ERROR_STATE AS ERROR_STATE')
, (' , @ERROR_LINE AS ERROR_LINE')
, (' , @ERROR_MESSAGE AS ERROR_MESSAGE')
, (' , @ERROR_PROCEDURE AS ERROR_PROCEDURE')
, ('')
, ('END CATCH;')
, ('')
IF @has_identity <> 0
BEGIN
INSERT @lines VALUES
('-- Turn off identity insert if it is on')
, ('IF @identity_insert = 1')
, ('BEGIN')
, (' SET IDENTITY_INSERT ' + QUOTENAME(@table) + ' OFF;')
, (' DBCC CHECKIDENT(' + QUOTENAME(@table) + ', RESEED);')
, ('END')
, ('');
END;
SELECT line FROM @lines ORDER BY id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment