CODEWITHSUNDEEP
sqlsql-server
Ronald
Ronald

Reputation: 1328

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

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?

Upvotes: 2

Views: 1836

Answers (1)

Liam
Liam

Reputation: 29624

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

Upvotes: 1

Related Questions