👀 Views: 36 💬 Answers: 1 📅 Created: 2025-06-06

sql-server azure-sql-database merge output-clause sql

I'm collaborating on a project where I recently switched to I'm converting an old project and I'm relatively new to this, so bear with me... I'm stuck on something that should probably be simple. I'm trying to use the `OUTPUT` clause with a `MERGE` statement in Azure SQL Database, but I'm working with an behavior that states, "The target table 'TableName' does not have a primary key." I am using SQL Server 2019 and have confirmed that my target table does indeed have a primary key defined. Here is my current SQL code: ```sql MERGE INTO TargetTable AS target USING (SELECT Id, Value FROM 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 @OutputTable; ``` I also have the `@OutputTable` declared as follows: ```sql DECLARE @OutputTable TABLE (Action NVARCHAR(10), Id INT, Value NVARCHAR(100)); ``` I verified that both `Id` and `Value` are part of the primary key in `TargetTable`. However, the behavior continues even after I double-checked the table schema. I have tried running it without the `OUTPUT` clause, and the `MERGE` executes successfully. Is there a limitation with using `OUTPUT` in a `MERGE` statement in Azure SQL that I might be missing? I would appreciate any insights or suggestions to resolve this scenario. Thanks in advance! What am I doing wrong? Has anyone dealt with something similar? I'm working in a CentOS environment. Is this even possible? Any pointers in the right direction?