0

I'm trying to duplicate some rows in a table, with a couple of changed values, and I also need to store an old (that will be lost) id to do further processing later. I'm trying to use the Output Clausule to store that information, but SQL Server is throwing the following error:

Msg 4104 <...> The multi-part identifier could not be bound.

This is the table I'm duplicating the data (slightly modified to reduce the number of columns):

Create Table Elements
(
    id              int Identity(0,1)   not null, --PK
    name            varchar(50)         not null,
    modelID         int                 not null, --FK

    constraint PK_Elements primary key (id)
);

And this is my my query:

declare @outputTable table 
(
    oldElementID    int,
    id              int,
    name            varchar(50),
    modelID         bigint
);

Insert into Elements
(name, modelID)
Output e.id as oldElementID, 
    Inserted.id,
    Inserted.name,
    Inserted.modelID into @outputTable
select e.name, @newModelID
from Elements as e
where e.modelID = @oldModelID

Note: @oldModelID and @newModelID are previously declared and set.

I'm not sure if my logic is wrong and I have to take a different approach (but I was sure it was possible to do it this way). Or if I simply have an error that I can't quite put my finger on it.

Any help would be appreciated.

Thanks!

MrZeruel
  • 53
  • 6
  • In addition to the linked duplicate, see also: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8cf1e38e-a29b-4ad7-abc7-b9fe2b987698/insert-into-using-select-with-output-into-multipart-identifier-could-not-be-bound?forum=transactsql – Tab Alleman Jul 26 '18 at 18:19
  • 1
    That actually works. Now I'm a bit concerned about performance, since in the "on" clausule I basically have to use <> instead of = to get the non-matching rows (I haven't tested it). But this works. Thanks! – MrZeruel Jul 26 '18 at 19:01

1 Answers1

0

I recreated the problem like this:

CREATE TABLE #a (a INT, b INT)

INSERT INTO #a (a,b) VALUES (42, 43)

INSERT INTO #a (a, b)
OUTPUT a.a, a.b, inserted.a, inserted.b
SELECT a.b, a.a
FROM #a a

The insert operation produces the messages:

Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "a.a" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "a.b" could not be bound.

that's because the INSERT command cannot see the alias 'a' that I used in the select command.

user1443098
  • 6,487
  • 5
  • 38
  • 67
  • I don't understand, the deleted pseudo-table gets filled on update and delete, this is an insert, using the information from a query. There is nothing deleted nor updated. – MrZeruel Jul 26 '18 at 18:18
  • oops. sorry! which identifier can't be found? Or are you autually seeing an ellipsis in msg 4104? – user1443098 Jul 26 '18 at 19:24
  • Reposted an answer. showing the problem that is. Essentially you can't get the output the way you want it. You could do it in two steps: 1. select the new values to your table variable, 2. Update the Elements table from your table variable. – user1443098 Jul 26 '18 at 19:33