OUTPUTting fields from an INSERT...SELECT query using fields from the SELECT results

Question

I'm trying to populate an association table with the output from an INSERT...SELECT query in Microsoft SQL Server, but I'm getting errors and can't find a workaround.

This is the most minimal, reproducible problem I can come up with: I have a table of things defined as:

CREATE TABLE dbo.things(
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    name nvarchar(20) NOT NULL UNIQUE
);

that is already populated. To allow the example to work, use this:

INSERT INTO dbo.things(
    name
)
VALUES('Thing1'),('Thing2'),('Thing3');

Now I have a table of widgets:

CREATE TABLE dbo.widgets(
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    description nvarchar(255) NULL
);

and a further table that associates things and widgets in a many-to-many manner:

CREATE TABLE dbo.thing_widget_associations(
    thing_id int NOT NULL REFERENCES dbo.things(id),
    widget_id int NOT NULL REFERENCES dbo.widgets(id),
    PRIMARY KEY(
        thing_id,
        widget_id
    )
);

However, each thing needs to have at least one widget association, but some of the necessary widgets are missing, so I'd like to create them, then associate them with the corresponding thing all in the same query.

Here's what I tried:

INSERT INTO dbo.widgets(
    description
)
OUTPUT
    INSERTED.id, -- Identifier of the newly created widget.
    t.id -- Identify of the associated thing. <- Doesn't work. I get error here.
INTO dbo.thing_widget_associations(
    widget_id,
    thing_id
)
SELECT
    'Default widget for thing ' + t.name
FROM dbo.thing_widget_associations AS twa
RIGHT JOIN dbo.things AS t
    ON twa.thing_id = t.id
WHERE twa.thing_id IS NULL;

However, when I try to run this query, I get the following:

Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "t.id" could not be bound.
Msg 332, Level 16, State 1, Line 1
The target table 'dbo.thing_widget_associations' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK__thing_wid__thing__5165187F'.

From what I've seen, it doesn't seem possible to use a field from a table queried as part of the INSERT query in the OUTPUT clause, and I think that's what the first error refers to. However, if I omit the OUTPUT clause, then I can't associate each created widget with the thing used to create it.

Can anyone also explain the second error, which has completely passed me by? Is that a consequence of the first error?

Bottom line: is there a way to do this? If so, what am I missing?

Use `merge`. see [this question](https://stackoverflow.com/questions/10949730/is-it-possible-to-for-sql-output-clause-to-return-a-column-not-being-inserted) — Stu, Apr 17 '21 at 21:36
Thanks, @Stu! Wow! I would never have guessed that in a million years! Any chance you could write that up as the answer, and I'll accept it immediately! — FunProg, Apr 17 '21 at 21:41

score 2 · Accepted Answer · answered Apr 17 '21 at 22:00

According to the documentation, the output of an insert can refer to just the inserted and deleted tables. Using merge is the workaround.

Here is how to restructure with merge

merge dbo.widgets t
using (
    select 'Default widget for thing ' + t.name w, t.id
    from dbo.things as t
    left join dbo.thing_widget_associations twa on twa.thing_id = t.id
    where twa.thing_id is null
)s on 1=0
when not matched then 
insert (description) values (s.w)
output INSERTED.id, s.id 
into dbo.thing_widget_associations(widget_id, thing_id);

note that with the sample table's constraints, this generates an error The target table 'dbo.thing_widget_associations' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK__thing_wid__thing__2BFE89A6'.

Therefore I've tested without these present on the table.

OUTPUTting fields from an INSERT...SELECT query using fields from the SELECT results

1 Answers1