CODEWITHSUNDEEP
sqlsql-server-2008
VasilisP
VasilisP

Reputation: 136

Insert Or Update After a Merge

I have the following MERGE operation on MS SQL Server.

DECLARE @data as xml
DECLARE @id as int
DECLARE @version as rowversion
SET @data = ?
SET @id = ?
<# if ( tw.local.enableOptimisticLocking == true ) { #>
SET @version = CAST(? as rowversion)
<# } #>
MERGE [<#=tw.local.dbSchema#>].[<#=tw.local.tableName#>] AS target
USING (Select @id as id, @version version ) as source ON target.id = source.id
WHEN MATCHED <# if ( tw.local.enableOptimisticLocking == true ) { #> AND target.version = source.version <# } #> THEN
    UPDATE SET data = @data
WHEN NOT MATCHED THEN
    INSERT (data) VALUES (@data)
OUTPUT $action as _action<# if ( tw.local.enableOptimisticLocking == true ) { #>, CAST( inserted.version as BigInt) as [version]<# } #>, inserted.id;

I wish to have an INSERT / UPDATE statement to another DB and table to update some columns based on the results of the above MERGE.

I am not sure if I can have the other INSERT/UPDATE inside the MERGE or do I need to use Output to get all data I want to INSERT/UPDATE from the MERGE?

I tried the following but it does not work....

DECLARE @data as xml
DECLARE @id as int
DECLARE @version as rowversion
SET @data = ?
SET @id = ?
<# if ( tw.local.enableOptimisticLocking == true ) { #>
SET @version = CAST(? as rowversion)
<# } #>
MERGE [<#=tw.local.dbSchema#>].[<#=tw.local.tableName#>] AS target
USING (Select @id as id, @version version ) as source ON target.id = source.id
WHEN MATCHED <# if ( tw.local.enableOptimisticLocking == true ) { #> AND target.version = source.version <# } #> THEN
    UPDATE SET data = @data
WHEN NOT MATCHED THEN
    INSERT (data) VALUES (@data)
OUTPUT $action as _action<# if ( tw.local.enableOptimisticLocking == true ) { #>, CAST( inserted.version as BigInt) as [version]<# } #>, inserted.id, inserted.version, inserted.data;

IF EXISTS (SELECT INSERTED.* FROM INSERTED LEFT JOIN DELETED ON INSERTED.ID = DELETED.id WHERE DELETED.ID IS NULL)
    BEGIN
        INSERT INTO [EMEAworkflowBPM].[cmf].[BusinessContextReporting] (id, version, data, updatedOn, toProcess)
        SELECT i.id, i.version, i.data, GETDATE(), 1
        FROM Inserted i
        LEFT JOIN [EMEAworkflowBPM].[cmf].[BusinessContextReporting] bcr
        ON i.id = bcr.id AND i.version = bcr.version
        WHERE bcr.id IS NULL;
    END
ELSE IF EXISTS (SELECT INSERTED.* FROM INSERTED INNER JOIN DELETED ON INSERTED.ID = DELETED.ID)
    BEGIN
        UPDATE [EMEAworkflowBPM].[cmf].[BusinessContextReporting]
        SET version = i.version, data = i.data, updatedOn = GETDATE(), toProcess = 1
            FROM Inserted AS i
            LEFT JOIN [EMEAworkflowBPM].[cmf].[BusinessContextReporting] AS bcr
            ON i.id = bcr.id;
    END

Many thanks in advance for your time and help.

Upvotes: 0

Views: 776

Answers (1)

Olle Johansson
Olle Johansson

Reputation: 518

You have to combine your OUTPUT with a INTO clause and make the UPDATEs and INSERTs as a new statement following the MERGE.

Take a look at the accepted answer in this question:

Using merge..output to get mapping between source.id and target.id

Upvotes: 1

Related Questions