CODEWITHSUNDEEP
sql-server
Mister Epic
Mister Epic

Reputation: 16723

SQL Merge - Column name or number of supplied values does not match table definition

I have an OUTPUT clause on a SQL merge that I've identified as the cause of this error:

Column name or number of supplied values does not match table definition

I am outputting to a temp table with a single field, and I'm flummoxed as to why I'm getting this error.

CREATE TABLE #PermissionChanges(
    ChangeType NVARCHAR(10)
)

MERGE INTO [dbo].[PermissionClient] AS Target
        USING (VALUES (@permissionId, @clientId, 1))
            AS Source ([PermissionId], [ClientId], [Enabled])   
        ON (Target.[ClientId] = @clientId AND Target.[PermissionId] = Source.[PermissionId])
        WHEN MATCHED THEN 
            UPDATE SET [Enabled] = Source.[Enabled]
        WHEN NOT MATCHED THEN
            INSERT ([PermissionId], [ClientId], [Enabled])
            VALUES (@permissionId, @clientId , Source.[Enabled])
        OUTPUT
             $ACTION ChangeType 
             INTO #PermissionChanges;

If I change the OUTPUT to this:

  OUTPUT $ACTION;

the script executes successfully and I get:

$ACTION
-------
UPDATE

Upvotes: 1

Views: 4022

Answers (3)

clovola
clovola

Reputation: 378

I had the same issue and found that specifying the column name which I wanted to OUTPUT the $Action into solved the issue, something I also saw in some of the MERGE documentation.

OUTPUT $ACTION ChangeType INTO #PermissionChanges(ChangeType)

Upvotes: 1

Eduard Uta
Eduard Uta

Reputation: 2607

Try removing ChangeType from the OUTPUT clause:

MERGE INTO [dbo].[PermissionClient] AS Target
    USING (VALUES (@permissionId, @clientId, 1))
        AS Source ([PermissionId], [ClientId], [Enabled])   
    ON (Target.[ClientId] = @clientId AND Target.[PermissionId] = Source.[PermissionId])
    WHEN MATCHED THEN 
        UPDATE SET [Enabled] = Source.[Enabled]
    WHEN NOT MATCHED THEN
        INSERT ([PermissionId], [ClientId], [Enabled])
        VALUES (@permissionId, @clientId , Source.[Enabled])
    OUTPUT
         $ACTION 
         INTO #PermissionChanges;

Upvotes: 0

Pavel Nefyodov
Pavel Nefyodov

Reputation: 896

Check your existing table structure as the following code:

USE tempdb
GO

-- Clean up
IF OBJECT_ID('tempdb.dbo.#PermissionChanges','u') is not null
BEGIN
DROP TABLE #results
END

CREATE TABLE #PermissionChanges(
    ChangeType NVARCHAR(10)
)
GO


-- Clean up
IF OBJECT_ID('tempdb.[dbo].[PermissionClient]','u') is not null
BEGIN
DROP TABLE #results
END

CREATE TABLE [dbo].[PermissionClient]
([PermissionId] int
, [ClientId] int
, [Enabled] int
)
GO

DECLARE @clientId int
SET @clientId=1
DECLARE @permissionId int
SET @permissionId=1


MERGE INTO [dbo].[PermissionClient] AS Target
        USING (VALUES (@permissionId, @clientId, 1))
            AS Source ([PermissionId], [ClientId], [Enabled])   
        ON (Target.[ClientId] = @clientId AND Target.[PermissionId] = Source.[PermissionId])
        WHEN MATCHED THEN 
            UPDATE SET [Enabled] = Source.[Enabled]
        WHEN NOT MATCHED THEN
            INSERT ([PermissionId], [ClientId], [Enabled])
            VALUES (@permissionId, @clientId , Source.[Enabled])
        OUTPUT
             $ACTION ChangeType 
             INTO #PermissionChanges;

SELECT * FROM #PermissionChanges

returns 

ChangeType
INSERT

Upvotes: 0

Related Questions