USE [master]
GO
IF OBJECT_ID('dbo.sp_PopulateProc') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_PopulateProc AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_PopulateProc
@Table VARCHAR(128) = NULL,
@ProcName VARCHAR(256) = NULL,
@CreateDelete BIT = 0,
@CreateInsert BIT = 1,
@DoNotUpdateIfIdentical BIT = 1,
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @Table = LTRIM(RTRIM(@Table))
DECLARE @object_id INT = OBJECT_ID(@Table)
IF @object_id IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_PopulateProc',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
DECLARE @ValuesSQL VARCHAR(MAX)
EXEC dbo.sp_TableValues
@Table = @Table,
@ValuesSQL = @ValuesSQL OUTPUT,
@PrintValuesSQL = 0,
@PrintUnionSQL = 0,
@PrintGenerateSQL = 0 ;
WITH MergeList
AS (SELECT TOP 999999
t.NAME AS TableName,
ISNULL(@ProcName, 'dbo.Populate' + t.NAME) AS ProcName,
'[' + s.NAME + '].[' + t.NAME + ']' AS TableFullName,
c.is_nullable,
CASE WHEN ic.column_id IS NOT NULL THEN 't.[' + c.name + '] = s.[' + c.name + ']'
ELSE NULL
END AS PrimaryKeyList,
CASE WHEN ic.column_id IS NULL THEN 't.[' + c.name + '] = s.[' + c.name + ']'
ELSE NULL
END AS UpdateList,
CASE WHEN ic.column_id IS NULL
AND c.is_nullable = 0 THEN 's.[' + c.name + '] <> t.[' + c.name + ']'
WHEN ic.column_id IS NULL
THEN '(s.[' + c.name + '] IS NULL AND t.[' + c.name + '] IS NOT NULL) OR (s.[' + c.name + '] IS NOT NULL AND t.[' + c.name
+ '] IS NULL) OR s.[' + c.name + '] <> t.[' + c.name + ']'
ELSE NULL
END AS DifferenceList,
'[' + c.name + ']' AS InsertList,
's.[' + c.name + ']' AS ValuesList,
CAST(c.is_identity AS INT) AS is_identity
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 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 t.OBJECT_ID = @object_id
ORDER BY c.column_id)
SELECT @SQL = 'IF OBJECT_ID(''' + ProcName + ''') IS NOT NULL DROP PROCEDURE ' + ProcName + '
GO
CREATE PROCEDURE ' + ProcName + '
AS
SET NOCOUNT ON
' + CASE WHEN SUM(is_identity) > 0 THEN 'SET IDENTITY_INSERT ' + TableFullName + ' ON
' ELSE ''
END + ';WITH ' + TableName + ' AS(' + ISNULL(@ValuesSQL, '@ValuesSQL') + ')
MERGE ' + TableFullName + ' as t
USING ' + TableName + ' AS s
ON ' + Util.dbo.StringConcat(PrimaryKeyList, ' AND ') + ISNULL('
WHEN MATCHED' + CASE WHEN @DoNotUpdateIfIdentical = 1 THEN ' AND (' + Util.dbo.StringConcat(DifferenceList, ' OR ') + ')'
ELSE ''
END + ' THEN
UPDATE SET ' + Util.dbo.StringConcat(UpdateList, ', '), '') + CASE WHEN @CreateInsert = 1 THEN '
WHEN NOT MATCHED THEN
INSERT(' + Util.dbo.StringConcat(InsertList, ', ') + ')
VALUES(' + Util.dbo.StringConcat(ValuesList, ', ') + ')' ELSE ''
END + CASE WHEN @CreateDelete = 1 THEN '
WHEN NOT MATCHED BY SOURCE THEN
DELETE' ELSE ''
END + ';' + CASE WHEN SUM(is_identity) > 0 THEN '
SET IDENTITY_INSERT ' + TableFullName + ' ON' ELSE ''
END + '
GO'
FROM MergeList
GROUP BY TableName,
TableFullName,
ProcName
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject
sp_PopulateProc
GO