USE [master]
GO
IF OBJECT_ID('dbo.sp_Merge') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_Merge AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_Merge
@Table SYSNAME = NULL,
@CreateDelete BIT = 1,
@CreateInsert BIT = 1,
@IncludeColumnList VARCHAR(MAX) = NULL,
@ExcludeColumnList VARCHAR(MAX) = NULL,
@DoNotUpdateIfIdentical BIT = 1,
@MaxColumnsInARow INT = 100,
@CreateRegion BIT = 0,
@QuoteName BIT = 1,
@SortColumnsAlpha BIT = 1,
@recipients NVARCHAR(1000) = NULL,
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT @CreateDelete = ISNULL(@CreateDelete, 1),
@CreateInsert = ISNULL(@CreateInsert, 1),
@DoNotUpdateIfIdentical = ISNULL(@DoNotUpdateIfIdentical, 1),
@MaxColumnsInARow = ISNULL(@MaxColumnsInARow, 100),
@QuoteName = ISNULL(@QuoteName, 1),
@CreateRegion = ISNULL(@CreateRegion, 0),
@SortColumnsAlpha = ISNULL(@SortColumnsAlpha, 1)
SET @MaxColumnsInARow = CASE WHEN ISNULL(@MaxColumnsInARow, 0) = 0 THEN 1
ELSE @MaxColumnsInARow
END
DECLARE @MaxCompareInARow INT = CASE WHEN @MaxColumnsInARow / 10 > 0 THEN @MaxColumnsInARow / 10
ELSE 1
END
DECLARE @ObjectID INT = OBJECT_ID(LTRIM(RTRIM(@Table)))
IF @ObjectID IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_Merge',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END ;
WITH Incl
AS (SELECT LTRIM(RTRIM(PARSENAME(Field, 1))) AS ColumnName
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@IncludeColumnList, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''),
Excl
AS (SELECT LTRIM(RTRIM(PARSENAME(Field, 1))) AS ColumnName
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@ExcludeColumnList, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''),
MergeList
AS (SELECT TOP 999999
Util.dbo.QuotenameOptional(@QuoteName, s.NAME) + '.' + Util.dbo.QuotenameOptional(@QuoteName, t.NAME) AS TableName,
c.is_nullable,
CASE WHEN ic.column_id IS NOT NULL
THEN 't.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + ' = s.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + ''
ELSE NULL
END AS PrimaryKeyList,
CASE WHEN ic.column_id IS NULL
THEN 't.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + ' = s.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + ''
ELSE NULL
END AS UpdateList,
CASE WHEN ic.column_id IS NULL
AND c.is_nullable = 0
THEN 's.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + ' <> t.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + ''
WHEN ic.column_id IS NULL
THEN '(s.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + ' IS NULL AND t.' + Util.dbo.QuotenameOptional(@QuoteName, c.name)
+ ' IS NOT NULL) OR (s.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + ' IS NOT NULL AND t.'
+ Util.dbo.QuotenameOptional(@QuoteName, c.NAME) + ' IS NULL) OR s.' + Util.dbo.QuotenameOptional(@QuoteName, c.name)
+ ' <> t.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + ''
ELSE NULL
END AS DifferenceList,
'' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + '' AS InsertList,
's.' + Util.dbo.QuotenameOptional(@QuoteName, c.name) + '' AS ValuesList,
NewLine,
CompNewLine
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
CROSS APPLY (SELECT CASE WHEN @MaxColumnsInARow = 1
OR (@MaxColumnsInARow > 1
AND c.column_id % @MaxColumnsInARow = 1) THEN '
' ELSE ''
END AS NewLine,
CASE WHEN @MaxCompareInARow = 1
OR (@MaxCompareInARow > 1
AND c.column_id % @MaxCompareInARow = 1) THEN '
' ELSE ''
END AS CompNewLine) nl
CROSS APPLY (SELECT TOP 1
i.OBJECT_ID,
i.index_id
FROM sys.indexes i (NOLOCK)
WHERE i.OBJECT_ID = t.OBJECT_ID
AND (i.is_primary_key = 1
OR i.is_unique_constraint = 1
OR i.is_unique = 1)
ORDER BY i.is_primary_key DESC,
i.is_unique_constraint DESC,
i.is_unique DESC,
TYPE ASC) i
LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.OBJECT_ID = i.OBJECT_ID
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
WHERE t.is_ms_shipped = 0
AND (ic.key_ordinal IS NOT NULL
OR (ic.key_ordinal IS NULL
AND (NOT EXISTS ( SELECT *
FROM incl )
OR EXISTS ( SELECT *
FROM incl
WHERE columnname = c.NAME ))
AND (NOT EXISTS ( SELECT *
FROM Excl )
OR NOT EXISTS ( SELECT *
FROM Excl
WHERE columnname = c.NAME ))))
AND t.OBJECT_ID = @ObjectID
ORDER BY ISNULL(ic.key_ordinal, 255),
CASE WHEN @SortColumnsAlpha = 1 THEN c.NAME
ELSE ''
END,
c.column_id)
SELECT @SQL = 'MERGE ' + TableName + ' as t
USING ' + TableName + ' AS s
ON ' + Util.dbo.StringConcat(PrimaryKeyList, ' AND ') + ISNULL('
WHEN MATCHED' + CASE WHEN @DoNotUpdateIfIdentical = 1
THEN CASE WHEN @CreateRegion = 1 THEN '
--#region Column Compare
' ELSE ' '
END + 'AND (' + Util.dbo.StringConcat(CompNewLine + DifferenceList, ' OR ') + ')' + CASE WHEN @CreateRegion = 1 THEN '
--#endregion Column Compare
' ELSE '
'
END
ELSE ''
END + ' THEN
', '') + CASE WHEN @CreateRegion = 1 THEN '--#region Update
' ELSE ''
END + ISNULL('UPDATE SET ' + Util.dbo.StringConcat(CompNewLine + UpdateList, ', '), '') + '
' + CASE WHEN @CreateRegion = 1 THEN '--#endregion Update
' ELSE ''
END + CASE WHEN @CreateInsert = 1 THEN '
WHEN NOT MATCHED THEN
--#region Insert
' ELSE ''
END + 'INSERT(' + Util.dbo.StringConcat(NewLine + InsertList, ', ') + ')
VALUES(' + Util.dbo.StringConcat(NewLine + ValuesList, ', ') + ')' + CASE WHEN @CreateRegion = 1 THEN '
--#endregion Insert
' ELSE ''
END + CASE WHEN @CreateDelete = 1 THEN '
WHEN NOT MATCHED BY SOURCE THEN
DELETE' ELSE ''
END + ';
'
FROM MergeList
GROUP BY TableName
IF @recipients <> ''
BEGIN
DECLARE @body NVARCHAR(MAX) = 'EXEC sp_Merge
@Table = ' + ISNULL('''' + @TABLE + '''', 'N/A') + ',
@CreateDelete = ' + CAST(@CreateDelete AS VARCHAR) + ',
@CreateInsert = ' + CAST(@CreateInsert AS VARCHAR) + ',
@IncludeColumnList = ' + ISNULL('''' + @IncludeColumnList + '''', 'NULL') + ',
@ExcludeColumnList ' + ISNULL('''' + @ExcludeColumnList + '''', 'NULL') + ',
@DoNotUpdateIfIdentical = ' + CAST(@DoNotUpdateIfIdentical AS VARCHAR) + ',
@MaxColumnsInARow = ' + CAST(@MaxColumnsInARow AS VARCHAR) + ',
@QuoteName = ' + CAST(@QuoteName AS VARCHAR) + ',
@SortColumnsAlpha = ' + CAST(@SortColumnsAlpha AS VARCHAR) + ',
@CreateRegion = ' + CAST(@CreateRegion AS VARCHAR) + ',
@recipients = ' + ISNULL('''' + @recipients + '''', 'NULL') + '
' + @SQL
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = 'sp_Merge',
@body = @body
END
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject
sp_Merge
GO