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 : granadacoder.wordpress.com/2008/12/10/… — granadaCoder
– granadaCoder, Commented Jan 13, 2014 at 18:18

M.Ali · Accepted Answer · 2014-01-13 17:27:21Z

7

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, 2014 at 17:27

answered Jan 13, 2014 at 17:11

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JJ. Over a year ago

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. Over a year ago

if i remove the where clause from above, it does work.

M.Ali Over a year ago

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 Over a year ago

@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.

Aaron Bertrand · Accepted Answer · 2014-01-13 17:11:46Z

1

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.

answered Jan 13, 2014 at 17:11

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related