April 26, 2021 at 10:15 am
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
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply