[SOLVED] How do I insert multiple columns into a table using OUTPUT in SQL Server?

How do I insert multiple columns into a table using OUTPUT in SQL Server?

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

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.