How do I insert multiple columns into a table using OUTPUT in SQL Server?
output-clause, sql, sql-server
Solution
put your `OUTPUT clause` right after you `INSERT INTO` statement something lie this....
INSERT INTO TableName (Column1, Column2, Column3.....)
OUTPUT inserted.Column1 , inserted.Column2 INTO @TableName
SELECT Column1, Column2, Column3.....
FROM Table_name
On a side note I would suggest you to keep `XML Shredding` and your `INSERT` operations in separate statements. any hanky panky in your XML can leave you with a lot of cleaning to do :)
do the shredding separately insert results into a temp table, once happy then insert into your target tables.
Problem
Scroll down to the OUTPUT part. It's giving me a red underline. I want to insert into the `InsertedCreditDebitAdjustmentIDs` table, the ID inserted into `@CreditDebitAdjustment` along with the InvoiceNum. How do I go about doing this? ``` DECLARE @InsertedCreditDebitAdjustmentIDs TABLE ( ID INT, InvoiceNumber VARCHAR(50) ) INSERT INTO @CreditDebitAdjustment ( col1, col2, ...) SELECT @ImportedFileID AS '@ImportedFileID', tbl.col.value('(Purpose/text())[1]', 'VARCHAR(500)') AS Purpose, etc. FROM @XML.nodes('/CreditDebitAdjustments/CreditDebitAdjustment') AS tbl (col) OUTPUT INSERTED.CreditDebitAdjustmentID, etc. INTO @InsertedCreditDebitAdjustmentIDs ```