CODEWITHSUNDEEP
sql-server-2008t-sqlstored-proceduresetl
Volvox
Volvox

Reputation: 621

Severe error on MERGE OUTPUT statement

I'm getting a severe error - code 20, from a Merge statement and I am not sure why. The MERGE runs fine if I remove the OUTPUT $action clause, and updates/inserts the target table as expected. As soon as I put the OUTPUT clause in, either as a simple OUTPUT $action or storing it in a table variable and selecting from that, it causes an error level 20. I am using two CTEs for the target and source. Not sure if that's contributing to the issue, but as I said, it works fine without an OUTPUT clause. Using SQL Server 2008. I've removed most of the columns for readability here.

USE [DM]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MergeMDATEST]

@InsertAuditKey INT = 10
,@UpdateAuditKey INT = 1

AS
SET NOCOUNT ON;

DECLARE @RowCounts TABLE (mergeAction varchar(20));
--------------------------------------------
--- CTEs to add in Binary_Checksum Calcs ---
WITH SourceQuery AS
(
SELECT TOP 100
    [Company]
    ,[AccountNumber]
    ,BINARY_CHECKSUM([Company]) As [Checksum]
FROM #temptestMergeSource
)

,TargetTable AS
(
SELECT TOP 100
    [Company]
    ,BINARY_CHECKSUM([Company]) As [Checksum]
FROM #temptestMergeTarget
)

MERGE TargetTable AS Target
USING SourceQuery As Source
    ON (
        Target.[AccountIndex] = Source.[AccountIndex]
        AND Target.[CodeID] = Source.[CodeID]
        AND Target.[GroupID] = Source.[GroupID]
        )
WHEN MATCHED AND (Target.[Checksum] <> Source.[Checksum]) THEN 
    UPDATE SET
            [AccountNumber] = Source.[AccountNumber]
            ,[AuditKey] = @UpdateAuditKey

WHEN NOT MATCHED THEN 
    INSERT (
            [Company]
            ,[AuditKey]
            )
    VALUES (
            Source.[Company]
            ,@InsertAuditKey
            )
OUTPUT $action INTO @RowCounts;

SELECT * FROM @RowCounts
-----RowCounts returned for auditing
--SELECT
--  COUNT(CASE WHEN mergeAction = 'Insert' THEN 1 ELSE 0 END) As [INSERT] 
--  ,COUNT(CASE WHEN mergeAction = 'Update' THEN 1 ELSE 0 END) As [UPDATE]
--FROM @RowCounts

Upvotes: 1

Views: 734

Answers (1)

Volvox
Volvox

Reputation: 621

A calculated column in a CTE Target combined with an output clause results in a fatal error. Bug submission on Microsoft Connect

Upvotes: 5

Related Questions