CODEWITHSUNDEEP
sqlt-sqlinsert
that0th3rGuy
that0th3rGuy

Reputation: 1386

Use INSERT-OUTPUT to provide values for another INSERT

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.

Upvotes: 3

Views: 243

Answers (1)

Phil
Phil

Reputation: 42991

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')

Upvotes: 1

Related Questions