OUPUT for insert from table variable gives: "The multi-part identifier "k.CustomerName" could not be bound."

Question

I have seen many people here on stack overflow with this error message and all get it in another situation. I could not find my own situation among the already existing questions. So I hope someone can help me with this. I use SQL server 9 with SQL management studio 10.

--import the customers.
CREATE TABLE #AllCustomers(CustomerName NVARCHAR(100), CustomerNr NVARCHAR(16));
BULK INSERT #AllCustomers
FROM 'C:\allcustomers.txt' 
WITH 
( 
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n' 
);

DECLARE @DefinitionId int;

--get the id of the definition for which I have to set values.
SELECT @DefinitionId = pkDefinitionId
FROM dbo.Definitions
WHERE Name = 'DEFINITION-OF-MY-ITEM';

DECLARE @TempA TABLE (CustomerName NVARCHAR(255), CustomerNr NVARCHAR(16));

--reduce the set of all customers to only the customer for whom I have to insert.
WITH MyView AS 
    (SELECT kciv.CustomerNr
     FROM dbo.CustomerItems kciv
     INNER JOIN dbo.DefinitionToItem civ ON civ.pkDefinitionToItemId = kciv.pkCustomerItemId
     WHERE civ.fkDefinitionId = @DefinitionId)
INSERT INTO @TempA 
SELECT k.CustomerName , k.CustomerNr 
FROM #AllCustomers k
WHERE k.CustomerNr NOT IN (SELECT CustomerNr FROM MyView);

--used to store the generated primairy keys I need for creating relations.
DECLARE @ItemIds TABLE (CustomerName NVARCHAR(255), CustomerNr NVARCHAR(16), pkItemId int);
DECLARE @DefinitionToItemIds TABLE (CustomerName NVARCHAR(255), CustomerNr NVARCHAR(16), pkDefinitionToItemId int);

--insert the default values.
INSERT INTO dbo.Items
OUTPUT k.CustomerName, k.CustomerNr, inserted.pkItemId 
  INTO @ItemIds (CustomerName, CustomerNr, pkGenericValueId)
SELECT 2, 1, null, null, 1, null
FROM @TempA k;

--couple the values to the definition.
INSERT INTO dbo.DefinitionToItem
OUTPUT gvd.CustomerName, gvd.CustomerNr, inserted.pkDefinitionToItemId 
  INTO @DefinitionToItemIds
SELECT 1, 0, @DefinitionId, gvd.pkItemId
FROM @ItemIds gvd;

--couple the 'coupling' to the customers.
INSERT INTO dbo.CustomerItems
SELECT civd.pkDefinitionToItemId, civd.CustomerName, civd.CustomerNr
FROM @DefinitionToItemIds civd;

I get four errors when running the query, all on the two output lines near the end of the code sample.

Msg 4104, Level 16, State 1, Line 64 The multi-part identifier "k.CustomerName" could not be bound.
Msg 4104, Level 16, State 1, Line 64 The multi-part identifier "k.CustomerNr" could not be bound.
Msg 4104, Level 16, State 1, Line 69 The multi-part identifier "gvd.CustomerName" could not be bound.
Msg 4104, Level 16, State 1, Line 69 The multi-part identifier "gvd.CustomerNr" could not be bound.

I have checked for typos but couldn't find any (I might have introduced some here though while changing some of the names to remove the context). I can't find out why this is going wrong. I've looked at MSDN, but I can't find anything wrong.

Extra info:

The database schema is as follows:

• The Items table contains "values" (pkItemId, bunch of other columns)
• The Definition table contains "definitions" (pkDefinitionId, Name, bunch of other columns)
• The DefinitionToItem table matches the values to definitions (pkDefinitionToItemId, fkDefinitionId, fkItemId)
• The CustomerItems table links a customer to a DefinitionToItemId (pkDefinitionToItemId, CustomerName, CustomerNr).

What I need to achieve is to insert default values (i.e. "2, 1, null, null, 1, null" linked to definitino 'DEFINITION-OF-MY-ITEM') into the items database for a given set of customers. Some might already have a value for that definition and then I should skip them (hence the @TempA). So I insert the value into Items, then insert the coupling between definition and items in DefinitionToItem and lastly couple the customer to the DefinitionToItem by inserting into the DefinitionToItem table.

If there if a better way to achieve this than through what I'm doing, then I'm open to suggestions.

Answer 1

I think the approach you are taking is complicating the scenario.

Firstly, the OUTPUT clause is not going to work the way you need it to work here, because you can only use columns inserted, and you want to use columns from the source table, but ones that does not get inserted. (CustomerName as an example).

There are two ways I suggest you can go about this:

1. First Approach. Change you query. Use OUTPUT, but output an id field. Then, after your first insert into ITEMS, join your new table with your source table. NOW you will have access to all fields and still a way to identify which records should be inserted.

2. Second approach. Drop your temp tables. Use a simple insert statement with a WHERE _ NOT IN clause. This takes away the complexity and still achieves the goal of not inserting duplicates, just on a closer level.

Answer 2

Use:

INSERT INTO dbo.Items
OUTPUT INSERTED.CustomerName, INSERTED.CustomerNr, INSERTED.pkItemId 
  INTO @ItemIds (CustomerName, CustomerNr, pkGenericValueId)
SELECT 2, 1, null, null, 1, null
  FROM @TempA k;

INSERT INTO dbo.DefinitionToItem
OUTPUT INSERTED.CustomerName, INSERTED.CustomerNr, INSERTED.pkDefinitionToItemId 
  INTO @DefinitionToItemIds
SELECT 1, 0, @DefinitionId, gvd.pkItemId
  FROM @ItemIds gvd;

You might need to change the column names, because they need to come from the table being inserted into rather than the column they were sourced from.

Answer 3

Your table #AllCustomers does not contain field CustomerName in this query

SELECT k.CustomerName , k.CustomerNr 
FROM #AllCustomers k