How to create MERGE statement with variable in SQL Server

Question

I am trying to create a stored procedure that contains a merge statement. I want the merge statement to be able to use the variable @TargetTable as target but it is asking me for a table variable. This is my code:

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

I have tried using a table variable by passing @TargetTable as one of the data and thought that it's possible to use @TargetTable from the temporary table but I have no idea how to write the code

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

I have only seen examples stating the target table but not as variables.

Is there any way to do this?

Thanks in advance

You cannot parametrize the table (or column) names to be used in a T-SQL statement. If you must do this, then you have to use *dynamic SQL* to build the T-SQL statement inside your stored procedure, as a string, and then execute that string representing the statement — marc_s, May 29 '13 at 15:11

score 2 · Answer 1 · answered May 21 '15 at 16:38

I had this same issue recently and wrote a stored procedure to automate the MERGE statement creation and fire off sp_executesql for the results. The reason for the CTE on the source table was in my final work stored procedure, I linked to a logging table for incremental load processing. I also removed the Source Delete statement as my source used soft deletes. Feel free to add back in.

Here is the link to the blog post and SP against AW below. 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

score 1 · Answer 2 · answered Jun 06 '13 at 07:33

Thank you marc_s for your reply. I have tried using dynamic SQL to solve this problem and i found this website with good tutorials for writing dynamic SQL in stored procedure. [1]http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

I tried both methods and using sp_executesql didn't work for me. Using sp_executesql gives me an error that the table variable @TargetTbl is not declared.

and this worked for me:

CREATE PROCEDURE [dbo].[Update_Table]
(
    @TargetTbl          NVARCHAR(100)
)
AS
SET NOCOUNT ON
DECLARE @SQLquery   NVARCHAR(4000)
SET @SQLquery = 'MERGE INTO ' + @TargetTbl + ' AS t '+
    'USING dbo.SOURCE_TABLE AS s ON t.name = s.name '+
    'WHEN MATCHED AND (t.record != s.record) '+
    'THEN UPDATE SET t.record = s.record '+
    'WHEN NOT MATCHED '+
    'THEN INSERT VALUES (s.name, s.record) '+
    'WHEN NOT MATCHED BY SOURCE THEN DELETE '+
    'OUTPUT $action as ACTION,'+ 
    'DELETED.name AS Targetname,'+ 
    'DELETED.record AS Targetrecord,'+ 
    'INSERTED.name AS Sourcename,'+ 
    'INSERTED.record AS Sourcerecord;'

IF(@@ERROR = 0)     
    EXECUTE(@SQLquery)
ELSE
GoTo ErrorHandler

Set NoCount OFF
Return(0)

ErrorHandler:
    Return(@@ERROR)

Every string has to be in NVARCHAR for the code to work

You say "every string must be in `NVARCHAR`" for it to work, but all of your string literals aren't. To specify a string literal as `NVARCHAR`, you'd need to prefix the opening quote with an `N`, e.g. `N'This is an NVARCHAR literal'`. Alternatively, your assertion is incorrect. — Damien_The_Unbeliever, Jun 19 '13 at 09:49
you may be right. I am not sure if I still have to add the prefix N since I've declared SQLquery as NVARCHAR. What's important was that TargetTbl is NVARCHAR and SQLquery also NVARCHAR — shawnl_28, Jul 02 '13 at 08:29

score 0 · Answer 3 · answered Nov 15 '18 at 17:24

I made an improvement at the Tommy Swift answer to use the same script in different databases

enjoy

/* 
==============================================================================
Author:         Tommy Swift & Krisnamourt
Name:           spDynamicMerge
Create date:    5/18/2015
Update date:    15/11/2018
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' 
                @sourceDb source database
                @targetDb target database
                @sourceTb sourde table
                @targetTb 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]
    @sourceDb VARCHAR(100) ,
    @targetDb VARCHAR(100) ,
    @schemaName VARCHAR(100) = 'dbo',
    @sourceTb VARCHAR(8000),
    @targetTb 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)

    create table #columns (name varchar(400))

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

    truncate table #columns
    insert into #columns
        exec ('SELECT DISTINCT c.name
    FROM '+@sourceDb+'.sys.tables t
        INNER JOIN '+@sourceDb+'.sys.schemas s
            on s.schema_id = t.schema_id
        LEFT JOIN '+@sourceDb+'.sys.columns c
            ON t.object_id = c.object_id  
        LEFT JOIN '+@sourceDb+'.sys.indexes i
            ON i.object_id = c.object_id    
        LEFT JOIN '+@sourceDb+'.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+'.'+@sourceTb+'''')

    --Get List of non-PK columns for Updates
    SELECT @nonPKColumnsTarget = COALESCE(@nonPKColumnsTarget + ', ', '') + 'Target.' + name
        ,  @nonPKColumnsSource = COALESCE(@nonPKColumnsSource + ', ', '') + 'Source.' + name
        ,  @nonPKColumnsCompare = COALESCE(@nonPKColumnsCompare + ', ', '') + 'Target.' + name + ' = ' + 'Source.' + name
    FROM #columns

    truncate table #columns
    insert into #columns
    exec ('SELECT DISTINCT c.name
    FROM '+@sourceDb+'.sys.tables t
        INNER JOIN '+@sourceDb+'.sys.schemas s
            on s.schema_id = t.schema_id
        INNER JOIN  '+@sourceDb+'.sys.columns c
            ON t.object_id = c.object_id      
    WHERE s.name + ''.'' + t.name ='''+@schemaName+'.'+@sourceTb+'''')


    -- Create comma delimited column listing
    SELECT @columnListingTarget = COALESCE(@columnListingTarget + ', ', '') + name
        , @columnListingSource = COALESCE(@columnListingSource + ', ', '') + 'Source.'+ name    
    FROM #columns


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

    SELECT @sqlCommand = 
    'WITH temp AS ' + CHAR(13) + CHAR(10) + 
    '(' + CHAR(13) + CHAR(10) +
    ' SELECT * FROM '+@sourceDb+'.' + @schemaName + '.' + @sourceTb + ' WITH(NOLOCK) ' + CHAR(13) + CHAR(10) +      
    ') ' + CHAR(13) + CHAR(10) +
    'MERGE '+@targetDb+'.' + @schemaName + '.' + @targetTb  + ' 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

    drop table #columns

    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

How to create MERGE statement with variable in SQL Server

3 Answers3