T-SQL: Dynamic MERGE Script
Introduction
I want to share my dynamic merge script which it's useful for everyone. This script will generate merge script for replication of all tables. It must be the same table structures in both the database's tables. We used linked server's database for source data in this SQL script. We problem is replication data (no all of the data but more need more tables) production server to report server. We create linked server in report server which source is a production server. For this, we need to create replication procedure. Manually for this work, we spend a lot of time. For this problem create dynamic merge script.
[LOCAL_DB_NAME] - This DB_name located in report server which we need replicated data here.
[LINKEDSERVERNAME]- Name link server for the source data.
[DB_NAME]- Source database name
USE [LOCAL_DB_NAME]
GO
DECLARE @linkedserver NVARCHAR(100)= '[LINKEDSERVERNAME]', @dbname NVARCHAR(100)= '[DB_NAME]', @lndb NVARCHAR(100);
SET @lndb = IIF(@linkedserver IS NULL, '', @linkedserver + '.') + @dbname + '.';
WITH cte(lvl,
object_id,
name)
AS (SELECT 1,
object_id,
name
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
AND is_ms_shipped = 0
UNION ALL
SELECT cte.lvl + 1,
t.object_id,
t.name
FROM cte
JOIN sys.tables AS t ON EXISTS
(
SELECT NULL
FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = t.object_id
AND fk.referenced_object_id = cte.object_id
)
AND t.object_id <> cte.object_id
AND cte.lvl < 30
WHERE t.type_desc = 'USER_TABLE'
AND t.is_ms_shipped = 0),
level --this dependency level
AS (SELECT name,
MAX(lvl) AS dependency_level
FROM cte
GROUP BY name)
,
cte_pk -- this tables have a primary key
as
(
SELECT Col.Column_Name,col.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
)
,cte_identity
as
(SELECT
[schema] = s.name,
[table] = t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.identity_columns
WHERE [object_id] = t.[object_id]
)
)
SELECT table_name
,'/*------'+ cast(row_number() over (order by (select 1)) as nvarchar(10))+'------*/'
+iif(exists(select 1 from cte_identity where [table]=table_name),'SET IDENTITY_INSERT '+ table_schema + '.' + TABLE_NAME + ' ON;','') +
'MERGE INTO ' + table_schema + '.' + TABLE_NAME + ' AS TGT USING ' + @lndb + table_schema + '.' + TABLE_NAME + ' AS SRC ON ' +
(
SELECT STUFF(
(
SELECT CAST(IIF( --in this iif check primary key columns count in table if more one i need use statemnt 'and'
(
SELECT COUNT(*)
FROM cte_pk where
Table_Name = Tabb.Table_Name
) = 1, ' ', ' and ') AS VARCHAR(MAX)) + 'src.' + COLUMN_NAME + '= tgt.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.columns clm
WHERE TABLE_NAME = tabb.TABLE_NAME
AND EXISTS
(
SELECT 1
FROM cte_pk where Column_Name=clm.COLUMN_NAME
AND Table_Name = clm.TABLE_NAME
)
FOR XML PATH('')
), 1, IIF(
(
SELECT COUNT(*)
FROM cte_pk where
Table_Name = Tabb.Table_Name
) = 1, 1, 4), '') --in this iif check primary key columns count in table if more one we need use xml path symbol count =4
) + ' WHEN MATCHED AND EXISTS( SELECT SRC.* EXCEPT SELECT TGT.* )
THEN UPDATE SET ' + -- in this case check updated data and choosing columns of table without primary key
(
SELECT STUFF(
(
SELECT CAST(',' AS VARCHAR(MAX)) + 'tgt.' + COLUMN_NAME + '= src.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.columns clm
WHERE TABLE_NAME = tabb.TABLE_NAME
AND NOT EXISTS
(
SELECT 1
FROM cte_pk where Column_Name=clm.COLUMN_NAME
AND Table_Name = clm.TABLE_NAME
) FOR XML PATH('')
), 1, 1, '')
) + ' WHEN
NOT MATCHED THEN INSERT (' + --in this case for insert.
(
SELECT STUFF(
(
SELECT CAST(',' AS VARCHAR(MAX)) + COLUMN_NAME
FROM INFORMATION_SCHEMA.columns clm
WHERE TABLE_NAME = tabb.TABLE_NAME
--AND NOT EXISTS -- this case depend on you target server. If target server tables primary key have a default data (identity, new_id()) you need use this condition.
-- (
-- SELECT 1
-- FROM cte_pk where Column_Name=clm.COLUMN_NAME
-- AND Table_Name = clm.TABLE_NAME
-- )
FOR XML PATH('')
), 1, 1, '')
) + ') VALUES (' +
(
SELECT STUFF(
(
SELECT CAST(',' AS VARCHAR(MAX)) + 'src.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.columns clm
WHERE TABLE_NAME = tabb.TABLE_NAME
--AND NOT EXISTS -- this case depend on you target server. If target server tables primary key have a default data (identity, new_id()) you need use this condition.
-- (
-- SELECT 1
-- FROM cte_pk where Column_Name=clm.COLUMN_NAME
-- AND Table_Name = clm.TABLE_NAME
-- )
FOR XML PATH('')
), 1, 1, '')
) + ')
WHEN NOT MATCHED BY SOURCE THEN DELETE
;' +-- in this case for delete data. If don't need delete add here comment
iif(exists(select 1 from cte_identity where [table]=table_name),'SET IDENTITY_INSERT '+ table_schema + '.' + TABLE_NAME + ' OFF;','') ide
FROM INFORMATION_SCHEMA.TABLES tabb
JOIN level ON tabb.TABLE_NAME = level.name
WHERE TABLE_TYPE = 'BASE TABLE' --and TABLE_SCHEMA in ('rel','list')
and EXISTS(select 1 from cte_pk where TABLE_NAME=tabb.TABLE_NAME)
ORDER BY level.dependency_level;
After running the script return two-column result: table_name and script_for_merge. Choose all row for column script_for_merge create your replication procedure .
NOTES
- Support all tables have a primary key column
- Script support datatype which set operators support example: datatype shape not supported
- End of table name has space script not supported
See also
SQL Server 2008+: Merge (Unity is Strength - Strengthen your Performance by Merge)
Working with T-SQL MERGE statement (C#)
References
MSDN MERGE (Transact-SQL)
https://stackoverflow.com/questions/40388903/how-to-list-tables-in-their-dependency-order-based-on-foreign-keys
I hope, this article will be useful for you