How to Use the OUTPUT of an Insert Query for another insert query

Question

I'm trying to insert 2 records into 2 tables by using a subquery, but it gives me a syntax error.

Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'INSERT'. Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'.

The query that I'm trying to execute is

INSERT INTO [Files] ([FileTransformationId],[FileTypeEnumId]) 
VALUES 
(
   (INSERT INTO [FileTransformations] OUTPUT INSERTED.FileTransformationId DEFAULT VALUES),
   2
)

Is this possible?

No idea what you want... Please read How to ask a good SQL question and How to create a MCVE — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 23, 2018 at 8:56
What I want is to insert a record into a table and insert a second record into another table with the autoincremental id from the first statement in a single statement — Ronald
– Ronald, Commented Jan 23, 2018 at 9:06
Read about SCOPE_IDENTITY, IDENT_CURRENT and @@IDENTITY — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 23, 2018 at 9:14

Liam · Accepted Answer · 2018-01-23 09:25:52Z

1

You need to store the output in a variable first. So, I think you want something like this:

DECLARE @variable TABLE (value INT)--change type depending on your need

INSERT INTO [FileTransformations] 
OUTPUT INSERTED.FileTransformationId INTO @variable(value)
VALUES(.....)

INSERT INTO [Files] ([FileTransformationId],[FileTypeEnumId]) 
SELECT value, 2 FROM @variable

See How do I use an INSERT statement's OUTPUT clause to get the identity value? for more info on using output variables and Insert into table from table variable? for then inserting from that table variable.

Another option, if you just want the id of the last inserted record is to use scope_identity(). So you could write the above as:

INSERT INTO [FileTransformations] 
VALUES(.....)

INSERT INTO [Files] ([FileTransformationId],[FileTypeEnumId]) 
VALUES scope_identity(), 2

edited Jan 23, 2018 at 9:25

answered Jan 23, 2018 at 8:58

Liam

30k28 gold badges143 silver badges206 bronze badges

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

6 Comments

Ronald Over a year ago

if I try to execute the insert statement with the into clause, it gives me the error Must declare the table variable "@variable", so i guess it needs to be a temp table or should it be possible for @variable to be a simple int variable?

Denis Rubashkin Over a year ago

@variable must be a table variable

Liam Over a year ago

Yes, I missed that @DenisRubashkin, not something I use often

Liam Over a year ago

@Ronald, yes, sorry. I got the syntax for the table variable output wrong

Cardinal System Over a year ago

Any there any performance drawbacks for using the table variable?

|

Collectives™ on Stack Overflow

How to Use the OUTPUT of an Insert Query for another insert query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related