Use INSERT-OUTPUT to provide values for another INSERT

Question

Good day,

I was wondering if it is possible to use an INSERT-OUTPUT statement in such a way as to provide the value(s) for another, outer, INSERT statement. That way values can be added to an entity table and an intersection table in a single statement - I hope I'm wording this effectively. For example:

INSERT INTO [#tblIntersect] ([Entity1ID], [Entity2ID])
VALUES
(
    INSERT INTO [#tblEntity1] ([Value])
    OUTPUT [inserted].[ID] AS [entity1ID], @entity2ID AS [entity2ID]
    VALUES ('One')
)

So the inner INSERT-OUTPUT statement will add a new entity to table #tblEntity1. The new entity's ID (which is set as IDENTITY(1, 1) will then be returned through the OUTPUT statement, along with a static value (which I already have in my code), to provide the two values for the outer INSERT statement.

The reason I think it might be possible is because execution of the inner INSERT-OUTPUT statement on its own returns a table anyway, and such output can usually be used to provide values for INSERT statements.

Obviously this example doesn't work; I was hoping it's just a simple syntax problem.

Thank you in advance for any comments and advice.

Answer 1

Your requirement is possible according to the documentation.

Assuming #tblIntersect has two matching id columns this should work

INSERT INTO [#tblEntity1] ([Value])
OUTPUT [inserted].[ID] AS [entity1ID], @entity2ID AS [entity2ID]
   INTO #tblIntersect
VALUES ('One')