Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The multi-part identifier "Master.Buyer_Initial" could not be bound Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 2:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
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
Post #1389410
Posted Tuesday, November 27, 2012 2:27 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:49 PM
Points: 866, Visits: 7,478
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
Post #1389415
Posted Tuesday, November 27, 2012 2:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
Thanks... worked!
Post #1389420
Posted Wednesday, November 28, 2012 6:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
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!
Post #1389689
Posted Wednesday, November 28, 2012 6:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:22 AM
Points: 167, Visits: 691
Have you tried turning the IDENTITY_INSERT to ON?
Post #1389704
Posted Wednesday, November 28, 2012 6:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1389705
Posted Wednesday, November 28, 2012 6:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
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.
Post #1389719
Posted Wednesday, November 28, 2012 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1389724
Posted Wednesday, November 28, 2012 6:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
SET IDENTITY_INSERT dbo.tblTransactions ON - that worked... thanks!

Post #1389731
Posted Wednesday, November 28, 2012 7:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
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
Post #1389800
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse