2

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?

Sachin Rajput
  • 4,326
  • 2
  • 18
  • 29
Ronald
  • 1,278
  • 2
  • 10
  • 14
  • No idea what you want... Please read [How to ask a good SQL question](http://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056) and [How to create a MCVE](http://stackoverflow.com/help/mcve) – Shnugo Jan 23 '18 at 08: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 Jan 23 '18 at 09:06
  • 1
    Read about [`SCOPE_IDENTITY`, `IDENT_CURRENT` and `@@IDENTITY`](https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql) – Shnugo Jan 23 '18 at 09:14

1 Answers1

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
Liam
  • 27,717
  • 28
  • 128
  • 190