Geek asks and answers

Use INSERT-OUTPUT to provide values ​​for another INSERT

Good afternoon,

I was wondering if the INSERT-OUTPUT statement could be used in such a way as to provide the value (s) for another external INSERT statement. Thus, values ​​can be added to the entity table and intersection table in one statement - I hope that I formulate this correctly. For instance:

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

Thus, the internal INSERT-OUTPUT statement will add a new object to the table #tblEntity1. A new object ID(which is set as IDENTITY(1, 1), will then be returned via the statement OUTPUTalong with a static value (which I already have in my code) to provide two values ​​for the external one INSERT.

I think this is possible, because executing the internal statement INSERT-OUTPUTitself returns a table anyway, and this output can usually be used to provide values ​​for statements INSERT.

Obviously this example does not work; I was hoping this was just a syntax issue.

Thanks in advance for any comments and advice.

+3
source share
1 answer

Your request is possible in accordance with the documentation .

Assuming #tblIntersect has two id collation columns, this should work

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

More articles:


All Articles