SQL Server throws an "The multi-part identifier could not be bound" error when INSERT SELECT [duplicate]

Question

I have two tables:

Sheet (Id, Name, CreatedById, CreatedOn)
SheetColumn (Id, SheetId, ColumnName)

I want to copy some rows from my Sheet table with newIds using givenIds. Then I want to insert new and old ids to a temp table variable. Then using this temp table I want to copy related SheetColumn rows with new ids. But OUTPUT line throws an error:

The multi-part identifier "OldRows.Id" could not be bound

How can I achieve this with this or another approach?

For this I wrote something like this:

DECLARE @TempSheetMapping TABLE  
(
    OldSheetId INT,
    NewSheetId INT
);

WITH OldRows AS 
(
    SELECT * 
    FROM Sheet
    WHERE Id IN (.....)
)
INSERT INTO Sheet (Name, CreatedById, CreatedOn)
-- ERROR: The multi-part identifier "OldRows.Id" could not be bound.
OUTPUT inserted.Id AS NewSheetId, OldRows.Id AS OldSheetId 
INTO @TempSheetMapping (NewSheetId, OldSheetId)
SELECT Name, CreatedById, CreatedOn
FROM OldRows;

INSERT INTO SheetColumn (SheetId, ColumnName)
SELECT t.NewSheetId, sc.ColumnName
FROM SheetColumn sc
JOIN @TempSheetMapping t ON sc.SheetId = t.OldSheetId;

The OUTPUT clause can only reference the Inserted pseudo table - it cannot reference other tables involved — marc_s
– marc_s, Commented Sep 28, 2024 at 14:13

YSFKBDY · Accepted Answer · 2024-09-28 14:28:16Z

0

I've solved it by using MERGE. Here is the final code:

DECLARE @TempSheetMapping TABLE (
    OldSheetId INT,
    NewSheetId INT
);

WITH OldRows AS (
    SELECT * FROM Sheet
    WHERE Id IN (.....)
)
MERGE INTO Sheet AS Target
USING OldRows AS Source
ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (Name, CreatedById, CreatedOn)
    VALUES (Name, CreatedById, CreatedOn)
OUTPUT inserted.Id as NewSheetId, OldRows.Id AS OldSheetId
INTO @TempSheetMapping (NewSheetId, OldSheetId);

INSERT INTO SheetColumn (SheetId, ColumnName)
SELECT t.NewSheetId, sc.ColumnName
FROM SheetColumn sc
JOIN @TempSheetMapping t ON sc.SheetId = t.OldSheetId;

answered Sep 28, 2024 at 14:28

YSFKBDY

9141 gold badge16 silver badges49 bronze badges

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

Collectives™ on Stack Overflow

SQL Server throws an "The multi-part identifier could not be bound" error when INSERT SELECT [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related