merge - 如何在 SQL Server 中创建带变量的 MERGE 语句

Question

我正在尝试创建一个包含 merge 语句的存储过程。我希望 merge 语句能够使用变量 @TargetTable 作为目标，但它要求我提供一个表变量。这是我的代码:

CREATE PROCEDURE dbo.mergetable
(
    @TargetTable nvarchar(255)
)
AS

SET NOCOUNT ON
BEGIN
MERGE INTO  @TargetTable AS t
USING dbo.SOURCE_TABLE AS s    
        ON t.name = s.name    
    WHEN MATCHED AND (t.record != s.record) THEN
        --Row exists and data is different
        UPDATE SET t.record= s.record
    WHEN NOT MATCHED BY TARGET THEN 
        --Row exists in source but not in target
        INSERT (name, record)
        VALUES (s.name, s.record)
    WHEN NOT MATCHED BY SOURCE THEN 
        --Row exists in target but not in source
       DELETE
        OUTPUT $action as ACTION, 
    DELETED.name AS Targetname, 
DELETED.record AS Targetrecord, 
INSERTED.name AS Sourcename, 
INSERTED.record AS Sourcerecord, 

SELECT @@ROWCOUNT; 
END

我尝试通过将 @TargetTable 作为数据之一传递来使用表变量，并认为可以使用临时表中的 @TargetTable 但我没有想法如何编写代码

    DECLARE @temp TABLE(temp varchar(50));
    INSERT @temp VALUES(@TargetTable)

我只看到示例说明目标表而不是变量。

有什么办法吗？

提前致谢

Answer 1

我最近遇到了同样的问题并编写了一个存储过程来自动创建 MERGE 语句并为结果触发 sp_executesql。源表上 CTE 的原因是在我最后的工作存储过程中，我链接到一个日志表以进行增量加载处理。我还删除了 Source Delete 语句，因为我的源使用了软删除。请随时添加回来。

下面是针对 AW 的博文和 SP 的链接。 Using Dynamic T-SQL to Create Merge Statements

/* 
==============================================================================
Author:		    Tommy Swift
Name:           spDynamicMerge
Create date:    5/18/2015
Description:	Stored Procedure to Create MERGE Statements from Source Table
                joining back to target tables on PK columns for CRUD statement
                comparisons
Parameters:     @schemaName - Default = 'dbo' 
				@tableName to be Merged.  
				Schema required if table schema name is other than 'dbo'
Assumptions:    - The parameter table exists on both the Source and Target 
                    and PK's are the same on both DB tables.
                - PK columns will be used to determine record existence.
                - SP resides on the Target database where the filtered list
                    of columns per table occur.  This ensures that only the
                    columns used in the Target are evaluated.
==============================================================================
*/

CREATE PROCEDURE [dbo].[spDynamicMerge]
	@schemaName VARCHAR(100) = 'dbo',
	@tableName VARCHAR(8000)
AS
BEGIN TRANSACTION	
	SET NOCOUNT ON;
	BEGIN TRY
    
    DECLARE  @pkColumnsCompare VARCHAR(8000)            
            ,@nonPKColumnsTarget VARCHAR(8000)
            ,@nonPKColumnsSource VARCHAR(8000)
            ,@nonPKColumnsCompare VARCHAR(8000)
            ,@columnListingSource VARCHAR(8000)
            ,@columnListingTarget VARCHAR(8000)
            ,@sqlCommand NVARCHAR(4000)

    
    --Get list of PK columns for Insert determination
    SELECT @pkColumnsCompare = COALESCE(@pkColumnsCompare + ' AND ', '') + 'Target.' + c.name + ' = ' + 'Source.' + c.name           
	FROM sys.indexes i 
        INNER JOIN sys.index_columns ic 
            ON ic.object_id = i.object_id 
				AND i.index_id = ic.index_id 
        INNER JOIN sys.columns c
            ON ic.object_id = c.object_id
                AND ic.column_id = c.column_id  
        INNER JOIN sys.tables t
            ON t.object_id = c.object_id     
		INNER JOIN sys.schemas s
			on s.schema_id = t.schema_id 
    WHERE i.is_primary_key = 1
		AND s.name + '.' + t.name = @schemaName + '.' + @tableName

    
	--Get List of non-PK columns for Updates
    SELECT @nonPKColumnsTarget = COALESCE(@nonPKColumnsTarget + ', ', '') + 'Target.' + c.name
        ,  @nonPKColumnsSource = COALESCE(@nonPKColumnsSource + ', ', '') + 'Source.' + c.name
        ,  @nonPKColumnsCompare = COALESCE(@nonPKColumnsCompare + ', ', '') + 'Target.' + c.name + ' = ' + 'Source.' + c.name
    FROM 
    (SELECT DISTINCT c.name
    FROM sys.tables t
        INNER JOIN sys.schemas s
			on s.schema_id = t.schema_id
		LEFT JOIN sys.columns c
            ON t.object_id = c.object_id  
        LEFT JOIN sys.indexes i
            ON i.object_id = c.object_id    
        LEFT JOIN sys.index_columns ic 
            ON ic.object_id = i.object_id 
                AND ic.column_id = c.column_id  
    WHERE ic.object_id IS NULL AND
        s.name + '.' + t.name = @schemaName + '.' + @tableName         
    ) c

    
    -- Create comma delimited column listing
    SELECT @columnListingTarget = COALESCE(@columnListingTarget + ', ', '') + c.name
        , @columnListingSource = COALESCE(@columnListingSource + ', ', '') + 'Source.'+ c.name    
    FROM 
    (SELECT DISTINCT c.name
    FROM sys.tables t
		INNER JOIN sys.schemas s
			on s.schema_id = t.schema_id
        INNER JOIN sys.columns c
            ON t.object_id = c.object_id      
    WHERE s.name + '.' + t.name = @schemaName + '.' + @tableName         
    ) c

    --select @pkColumnsCompare, @nonPKColumnsTarget, @nonPKColumnsSource, @nonPKColumnsCompare, @columnListingTarget, @columnListingSource

    SELECT @sqlCommand = 
	'WITH temp AS ' + CHAR(13) + CHAR(10) + 
	'(' + CHAR(13) + CHAR(10) +
	' SELECT * FROM AdventureWorks2012.' + @schemaName + '.' + @tableName + ' WITH(NOLOCK) ' + CHAR(13) + CHAR(10) +		
	') ' + CHAR(13) + CHAR(10) +
	'MERGE DataPatternsStage.' + @schemaName + '.' + @tableName  + ' AS Target ' + CHAR(13) + CHAR(10) +
     'USING temp AS Source ' + CHAR(13) + CHAR(10) +
        'ON ' + @pkColumnsCompare + CHAR(13) + CHAR(10) +
    ' WHEN MATCHED THEN ' + CHAR(13) + CHAR(10) +
       'UPDATE SET ' + @nonPKColumnsCompare + CHAR(13) + CHAR(10) +
    ' WHEN NOT MATCHED BY TARGET ' + CHAR(13) + CHAR(10) +
    'THEN ' + CHAR(13) + CHAR(10) +
       'INSERT (' + @columnListingTarget + ') ' + CHAR(13) + CHAR(10) +
       'VALUES (' + @columnListingSource + '); '

    --select @sqlCommand
    
    EXECUTE sp_executesql @sqlCommand

	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   @ErrorState
				   );

	END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

GO