The multi-part identifier "Master.Buyer_Initial" could not be bound

Question

Post reply

The multi-part identifier "Master.Buyer_Initial" could not be bound

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
November 27, 2012 at 2:17 pm

#285218

I'm trying to load part (by that I mean certain columns) into a new table, but I keep getting this:
Msg 4104, Level 16, State 1, Line 26
The multi-part identifier "Master.Buyer_Initial" could not be bound
USE [TrackIT]
GO
INSERT INTO [dbo].[tblTransactions]
(tblTransactions.Buyer_Initial,
tblTransactions.HeatTicketNumber,
tblTransactions.PO_Number,
tblTransactions.Transaction_Date,
tblTransactions.EndUserFirstName,
tblTransactions.EndUserLastName,
tblTransactions.EmployeeStatus,
tblTransactions.CostCenter,
tblTransactions.TermDate,
tblTransactions.License_Available,
tblTransactions.TransferFrom,
tblTransactions.TransferTo,
tblTransactions.EndUserLocation,
tblTransactions.EquipType,
tblTransactions.Quantity,
tblTransactions.Software_Description,
tblTransactions.Notes,
tblTransactions.Unit_Price,
tblTransactions.AllocationDate,
tblTransactions.Last_Renewal_Date,
tblTransactions.NextRenewalDate,
tblTransactions.LastRenewalPO,
tblTransactions.Transaction_Number)
VALUES
(Master.Buyer_Initial,
Master.HeatTicketNumber,
Master.PO_Number,
Master.Transaction_Date,
Master.EndUserFirstName,
Master.EndUserLastName,
Master.EmployeeStatus,
Master.CostCenter,
Master.TermDate,
Master.License_Available,
Master.TransferFrom,
Master.TransferTo,
Master.EndUserLocation,
Master.EquipType,
Master.Quantity,
Master.Software_Description,
Master.Notes,
Master.Unit_Price,
Master.AllocationDate,
Master.Last_Renewal_Date,
Master.NextRenewalDate,
Master.LastRenewalPO,
Master.Transaction_Number)
GO

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Assuming that Master is a table in you DB:

USE [TrackIT]

GO

INSERT INTO [dbo].[tblTransactions]

(tblTransactions.Buyer_Initial,

tblTransactions.HeatTicketNumber,

tblTransactions.PO_Number,

tblTransactions.Transaction_Date,

tblTransactions.EndUserFirstName,

tblTransactions.EndUserLastName,

tblTransactions.EmployeeStatus,

tblTransactions.CostCenter,

tblTransactions.TermDate,

tblTransactions.License_Available,

tblTransactions.TransferFrom,

tblTransactions.TransferTo,

tblTransactions.EndUserLocation,

tblTransactions.EquipType,

tblTransactions.Quantity,

tblTransactions.Software_Description,

tblTransactions.Notes,

tblTransactions.Unit_Price,

tblTransactions.AllocationDate,

tblTransactions.Last_Renewal_Date,

tblTransactions.NextRenewalDate,

tblTransactions.LastRenewalPO,

tblTransactions.Transaction_Number)

Select

Master.Buyer_Initial,

Master.HeatTicketNumber,

Master.PO_Number,

Master.Transaction_Date,

Master.EndUserFirstName,

Master.EndUserLastName,

Master.EmployeeStatus,

Master.CostCenter,

Master.TermDate,

Master.License_Available,

Master.TransferFrom,

Master.TransferTo,

Master.EndUserLocation,

Master.EquipType,

Master.Quantity,

Master.Software_Description,

Master.Notes,

Master.Unit_Price,

Master.AllocationDate,

Master.Last_Renewal_Date,

Master.NextRenewalDate,

Master.LastRenewalPO,

Master.Transaction_Number

from [Master]

GO

And then again, I might be wrong ...
David Webb

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

DaveBriCam

SSCarpal Tunnel

Points: 4298

November 27, 2012 at 2:34 pm

#1562857

Thanks... worked!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 3

One additional question... my receiving table has its primary key with "Is Identity" being "Yes" and and a seed of 18000 incremented by one... the number of records (historical data) that I'm importing is just under 18000... how can I upload that data when I get a message:

"Cannot insert explicit value for identity column in table 'tblTransactions' when IDENTITY_INSERT is set to OFF."?

Thanks!

bleroy Hall of Fame Points: 3052 More actions · Answer 4

bleroy

Hall of Fame

Points: 3052

November 28, 2012 at 6:29 am

#1563082

Have you tried turning the IDENTITY_INSERT to ON?

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Two ways,

Dont insert a value into the identity column of the destination

or

Add the below to the query

SET IDENTITY_INSERT tblTransactions ON

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 6

tried and get: Msg 1088, Level 16, State 11, Line 1

Cannot find the object "tblTransactions" because it does not exist or you do not have permissions.

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

Does the table exist?

Do you have permissions to the table?

What is your user accounts default schema? If it is different than that of the table, you will need to append the schema name to the object

SET IDENTITY_INSERT dbo.tblTransactions ON

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 8

SET IDENTITY_INSERT dbo.tblTransactions ON - that worked... thanks!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 9

Tried loading my data and got the same error.

Then tried SET IDENTITY_INSERT dbo.tblTransactions ON again and it said: Cannot find the object "dbo.tblTransactions" because it does not exist or you do not have permissions.

Strange since I have full admin rights on this DB

Ant-Green SSC Guru Points: 113507 More actions · Answer 10

Ant-Green

SSC Guru

Points: 113507

November 28, 2012 at 7:56 am

#1563169

Is dbo the right schema?

Gazareth One Orange Chip Points: 27737 More actions · Answer 11

Gazareth

One Orange Chip

Points: 27737

November 28, 2012 at 8:08 am

#1563179

Or are you in the right database?

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 12

Yes correct schema... fully qualified is TrackIT dbo tblTransactions... and I'm in the correct database in which I can run queries, add fields, query my data, redefine datatypes, write triggers, etc.