SQL Server 2019: scenarios while trying to use OUTPUT clause with MERGE statement
I'm relatively new to this, so bear with me. I've looked through the documentation and I'm still confused about After trying multiple solutions online, I still can't figure this out. I'm working on a project and hit a roadblock. I'm working with an scenario while attempting to use the OUTPUT clause with a MERGE statement in SQL Server 2019. The goal is to capture the changes made during the MERGE operation into a separate audit table. However, when executing the following code, I receive the behavior message: `Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'OUTPUT'.` Here’s the SQL code I’m working with: ```sql MERGE INTO TargetTable AS target USING SourceTable AS source ON target.Id = source.Id WHEN MATCHED THEN UPDATE SET target.Value = source.Value WHEN NOT MATCHED THEN INSERT (Id, Value) VALUES (source.Id, source.Value) OUTPUT $action, inserted.Id, inserted.Value INTO AuditTable; ``` I’ve verified that the `AuditTable` exists and has the necessary columns to match the OUTPUT structure. The `OUTPUT` clause is placed correctly based on documentation, yet this behavior continues. I've also tried rearranging the query, but it doesn't seem to help. Is there a specific limitation or requirement when using OUTPUT with MERGE in SQL Server 2019 that I might be missing? Any guidance on resolving this syntax behavior would be greatly appreciated! Am I missing something obvious? How would you solve this? Cheers for any assistance! I'm working with Sql in a Docker container on CentOS.