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.

something like last-insert-id does not work in this case to fetch it again and use the select result of it for another insert? — Hajo
– Hajo, Commented Apr 19, 2012 at 15:47
it probably would, I was just trying to write the shortest query with the least separate statements :P — that0th3rGuy
– that0th3rGuy, Commented Apr 19, 2012 at 15:57

Phil · Accepted Answer · 2012-04-19 16:16:57Z

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

answered Apr 19, 2012 at 16:16

Phil

43.2k9 gold badges102 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Use INSERT-OUTPUT to provide values for another INSERT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related