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

Question

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

Here is an example I wrote up : http://granadacoder.wordpress.com/2008/12/10/sqlserver20052008-output-clause-in-insertupdatedelete-statements/ — granadaCoder, Jan 13 '14 at 18:18

M.Ali · Accepted Answer · 2014-01-13T17:27:21.923

6

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.

edited Jan 13 '14 at 17:27

answered Jan 13 '14 at 17:11

M.Ali

67,945
13
101
127

what if I wanna do a where clause? This isn't working: OUTPUT INSERTED.RetailImportOrderID, INSERTED.ErrorMessage WHERE INSERTED.ErrorMessage = 'Nothing has changed. The order is 100% duplicated.' INTO @DeletablePODuplicates – JJ. Jan 13 '14 at 21:36
if i remove the where clause from above, it does work. – JJ. Jan 13 '14 at 21:37
you cannot use WHERE clause with OUTPUT, if you want to limit the rows you should use where clause in your select statement – M.Ali Jan 13 '14 at 21:37
@Rj. or once you have inserted data into your TableName through output, you can use where clause when selecting data from it again. OUTPUT clause doesnt allow you to limit the rows using where clause. – M.Ali Jan 13 '14 at 21:39

score 1 · Answer 2 · answered Jan 13 '14 at 17:11

It goes:

TARGET (insert/update etc.)
OUTPUT (direct or into another table)
SOURCE (select/values etc.)

So you just need to switch the order:

INSERT dbo.tablename(column1, column2, ...)
OUTPUT INSERTED.col1, INSERTED.col2, ... [INTO ...]
SELECT @var1, col1, etc. FROM elsewhere...

See the OUTPUT Clause (Transact-SQL) documentation for examples.

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

2 Answers2