SQL Server Using OUTPUT Clause Results in advanced patterns with Table Variables
I just started working with I'm running into an scenario where I'm trying to use the `OUTPUT` clause in a stored procedure to capture changes from an `INSERT` into a table variable, but it seems like the behavior is not as expected. My goal is to log the inserted records into another table, but the table variable doesn't seem to retain the expected results after the `INSERT` operation. Here’s a snippet of my stored procedure: ```sql CREATE PROCEDURE InsertAndLogRecords AS BEGIN DECLARE @InsertedRecords TABLE (Id INT, Value NVARCHAR(100)); INSERT INTO MyTable (Value) OUTPUT inserted.Id, inserted.Value INTO @InsertedRecords VALUES ('Test1'), ('Test2'); -- Attempting to select from the table variable to log SELECT * FROM @InsertedRecords; END ``` When I execute this stored procedure, I expect it to return the new IDs and values that were inserted into `MyTable`, but instead, I get an empty result set. The question seems to stem from how the `OUTPUT` clause interacts with table variables. I checked the documentation for SQL Server 2019, and it suggests this usage should work correctly, yet it's not behaving as anticipated. I've also verified that `MyTable` is not empty, and the `INSERT` operation does succeed without any errors. After some troubleshooting, I even tried using a temporary table instead of a table variable, and that produced the expected results: ```sql CREATE TABLE #InsertedRecords (Id INT, Value NVARCHAR(100)); ``` But I prefer to use a table variable for its scope limitations. Is there a known scenario or limitation with using the `OUTPUT` clause with table variables in SQL Server? Any insights or workarounds would be greatly appreciated! Any help would be greatly appreciated!