Post reply

Using CASE Statement with $Action in Output Clause in Merge

  • April 26, 2021 at 10:15 am

    #3875795

    Hi there

    I want to write one combined Output statement at the end of a Merge statement to insert data into an action table, based on the action

    So I want to do something like

    Select $action

    case when $action = 'Update'

    then Output $Action, Deleted.* into #DeviceDataDelta

    case when $action = 'Insert'

    then Output $Action, Inserted.* into #DeviceDataDelta

    end

    Whats the best way of doing this...so i only have 1 Output statement to either write data from the Deleted or Inserted table based on the action?

  • April 26, 2021 at 12:21 pm

    #3875836

    Yes, only 1 OUTPUT clause.  My best suggestion is to use CASE for every column.  You can generate the CASE statements from the table definition so you don't have to do them by hand, like below.  Then assemble the final OUTPUT clause:

    OUTPUT $Action, <code_generated_below>

    SELECT 'CASE WHEN $Action = ''Insert'' THEN INSERTED.[' + c.name + '] ELSE DELETED.[' + name + '] END,' 
    FROM sys.columns c 
    WHERE c.object_id = OBJECT_ID('dbo.your_table_name')
    ORDER BY c.column_id

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply