If you’re getting an error that reads “Invalid column name ‘$action’” in SQL Server, it could be that you’re using the $action argument of the OUTPUT clause for an operation that doesn’t support this argument.

Or it could be that the operation does support the argument, but you’re referencing it with the wrong syntax.

The $action argument can only be used with MERGE operations, and it must be done correctly.

To fix this issue, be sure to only use the $action argument in MERGE operations and to reference it with the correct syntax.

Example of Error

Here’s an example of code that produces the error:

INSERT INTO Customers (FirstName, LastName, Email)
OUTPUT 
    INSERTED.CustomerID,
    INSERTED.FirstName,
    INSERTED.LastName,
    $action
VALUES
    ('Rohit', 'Rooney', '[email protected]'),
    ('Selina', 'Bradey', '[email protected]'),
    ('Nelly', 'Nguyen', '[email protected]');

Output:

Msg 207, Level 16, State 1, Line 6
Invalid column name '$action'.

Here, I tried to use the $action argument in my OUTPUT clause, but I’m doing an INSERT operation. The $action argument isn’t supported in INSERT operations – only MERGE operations.

If this is happening to you, maybe you copied and pasted some OUTPUT code from a MERGE operation and you forgot to remove the $action part? Regardless, it shouldn’t be there.

We can also get the error while actually doing a MERGE:

MERGE TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID

WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name, 
               target.Quantity = source.Quantity

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name, Quantity) 
    VALUES (source.ID, source.Name, source.Quantity)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE

OUTPUT 
    "$action" AS Operation,
    DELETED.ID AS OldID, 
    DELETED.Name AS OldName, 
    DELETED.Quantity AS OldQuantity,
    INSERTED.ID AS NewID, 
    INSERTED.Name AS NewName, 
    INSERTED.Quantity AS NewQuantity;

Output:

Msg 207, Level 16, State 1, Line 17
Invalid column name '$action'.

In this case I (incorrectly) surrounded the $action argument in double quotes, which produced the error.

Solution

First of all, we must be sure to only use the $action argument where it’s supported – in the MERGE statement.

So when it comes to our INSERT statement, all we need to do is remove the $action part:

INSERT INTO Customers (FirstName, LastName, Email)
OUTPUT 
    INSERTED.CustomerID,
    INSERTED.FirstName,
    INSERTED.LastName
VALUES
    ('Rohit', 'Rooney', '[email protected]'),
    ('Selina', 'Bradey', '[email protected]'),
    ('Nelly', 'Nguyen', '[email protected]');

Output:

CustomerID  FirstName  LastName
----------  ---------  --------
1           Rohit      Rooney  
2           Selina     Bradey  
3           Nelly      Nguyen  

This time it worked.

As for the MERGE:

MERGE TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID

WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name, 
               target.Quantity = source.Quantity

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name, Quantity) 
    VALUES (source.ID, source.Name, source.Quantity)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE

OUTPUT 
    $action AS Operation,
    DELETED.ID AS OldID, 
    DELETED.Name AS OldName, 
    DELETED.Quantity AS OldQuantity,
    INSERTED.ID AS NewID, 
    INSERTED.Name AS NewName, 
    INSERTED.Quantity AS NewQuantity;

Output:

Operation  OldID  OldName    OldQuantity  NewID  NewName    NewQuantity
---------  -----  ---------  -----------  -----  ---------  -----------
UPDATE     2      Product B  45           2      Product B  45         
UPDATE     3      Product C  20           3      Product C  20         
UPDATE     4      Product D  60           4      Product D  60         

This one worked as expected too. All we did was remove the double quotes from the $action argument.