CODEWITHSUNDEEP
sqlsql-servert-sqlsql-updatesql-insert
ruohola
ruohola

Reputation: 24058

How to INSERT the results of OUTPUT to the same table in one query?

I have a table called ItemTable, which has the columns:

ID (identity col int), primary key, foreign key constraint with ProductTable

Col (varchar(50))

ColOther (varchar(50))

Latest (bit)

Time (autogenerated datetime)

I'm doing a query:

UPDATE ItemTable
SET Col = 'new', ColOther = 'newother'
OUTPUT deleted.Col, deleted.ColOther
WHERE ID = 100;

Which updates the row just fine and gives the correct output:

| Col       | ColOther |
| --------- |:--------:|
| old       | oldother |

 

I would like to INSERT this output (and the constant Latest = 0) to the same ItemTable in the same UPDATE statement.

I've tried:

INSERT INTO ItemTable (Col, ColOther, Latest)
UPDATE ItemTable
SET Col = 'new', ColOther = 'newother'
OUTPUT deleted.Col, deleted.ColOther, 0
WHERE ID = 100;

Which gives the error:

Incorrect syntax near the keyword 'UPDATE'.

And:

UPDATE ItemTable
SET Col = 'new', ColOther = 'newother'
OUTPUT deleted.Col, deleted.ColOther, 0 INTO ItemTable
WHERE ID = 100;

Which gives the error:

The target table 'ItemTable' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_ProductTable_ItemTable'.

Upvotes: 1

Views: 837

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82484

You can't do that in a single statement.
You have to use one statement for update and one for insert.
However, you can do it in a transaction, so that if the insert fails you can rollback the update:

DECLARE @updated TABLE (Col varchar(50), ColOther varchar(50))

BEGIN TRANSACTION
BEGIN TRY

UPDATE ItemTable
SET Col = 'new', ColOther = 'newother'
OUTPUT deleted.Col, deleted.ColOther INTO @updated
WHERE ID = 100

INSERT INTO ItemTable (Col, ColOther, Latest)
SELECT Col, ColOther, 0
FROM @updated

COMMIT TRANSACTION
END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH

Upvotes: 3

Related Questions