why is this giving me an error,can anyone help.

Laura Ijewere 41 Reputation points
2020-10-04T00:20:10.847+00:00

INSERT INTO [dbo].[FinalReportSource] (
[Row Id]
,[order id]
,[Order Date]
,[Date]
,[Ship Mode]
,[customer Id]
,[Customer Name]
,[Gender]
,[MaritalStatus]
,[Age]
,[Numberofchildren]
,[Segment]
,[Country]
,[City]
,[State]
,[Postal Code]
,[Region]
,[EmployeeID]
,[Person]
,[Type]
,[Weekly]
,[product Id]
,[Category]
,[SubCategory]
,[ProductName]
,[sales]
,[Quantity]
,[Discount]
)
SELECT [Row Id]
,od.[order id]
,od.[Order Date]
,od.[Date] AS ShipDate
,od.[Ship Mode]
,od.[customer Id]
,cd.[Customer Name]
,pd.[Gender]
,pd.[MaritalStatus]
,pd.[Age]
,pd.[Numberofchildren]
,cd.[Segment]
,cd.[Country]
,cd.[City]
,cd.[State]
,cd.[Postal Code]
,od.[Region]
,wd.[EmployeeID]
,wd.[Person]
,wd.[Type]
,wd.[Weekly]
,dp.[ProductID]
,dp.[Category]
,dp.[SubCategory]
,dp.[ProductName]
,od.[sales]
,od.[Quantity]
,od.[Discount]
SELECT TOP 1 *
FROM [dbo].[weeklyorghierarchydestination] AS WD
JOIN [dbo].[CustomerDestination] AS cd ON wd.Region = cd.[Region]
JOIN [dbo].[PersonalInfoDestination] AS Pd ON cd.[Customer ID] = Pd.[CustomerID]
JOIN [dbo].[orderdetailsDestinations] AS Od ON pd.[CustomerID] = Od.[customer Id]
JOIN [dbo].[Dimproduct] AS Dp ON od.[product Id] = Dp.ProductID

hi i am trying to insert into table finalreportsource from different tables using joins but keep getting this error for all the cols
The multi-part identifier "" could not be bound.
can anyone help please,how can I fix the error

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,524 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-05T04:38:30.96+00:00

    Hi @Laura Ijewere ,

    Actually you used two SELECTs in one statement which would cause 'The multi-part identifier "" could not be bound.' error.

    Please have a try with below query and let us know if any more error is reported.

    INSERT INTO [dbo].[FinalReportSource] (  
    [Row Id]  
    ,[order id]  
    ,[Order Date]  
    ,[Date]  
    ,[Ship Mode]  
    ,[customer Id]  
    ,[Customer Name]  
    ,[Gender]  
    ,[MaritalStatus]  
    ,[Age]  
    ,[Numberofchildren]  
    ,[Segment]  
    ,[Country]  
    ,[City]  
    ,[State]  
    ,[Postal Code]  
    ,[Region]  
    ,[EmployeeID]  
    ,[Person]  
    ,[Type]  
    ,[Weekly]  
    ,[product Id]  
    ,[Category]  
    ,[SubCategory]  
    ,[ProductName]  
    ,[sales]  
    ,[Quantity]  
    ,[Discount]  
    )  
    SELECT top 1 [Row Id]  
    ,od.[order id]  
    ,od.[Order Date]  
    ,od.[Date] AS ShipDate  
    ,od.[Ship Mode]  
    ,od.[customer Id]  
    ,cd.[Customer Name]  
    ,pd.[Gender]  
    ,pd.[MaritalStatus]  
    ,pd.[Age]  
    ,pd.[Numberofchildren]  
    ,cd.[Segment]  
    ,cd.[Country]  
    ,cd.[City]  
    ,cd.[State]  
    ,cd.[Postal Code]  
    ,od.[Region]  
    ,wd.[EmployeeID]  
    ,wd.[Person]  
    ,wd.[Type]  
    ,wd.[Weekly]  
    ,dp.[ProductID]  
    ,dp.[Category]  
    ,dp.[SubCategory]  
    ,dp.[ProductName]  
    ,od.[sales]  
    ,od.[Quantity]  
    ,od.[Discount]  
    FROM [dbo].[weeklyorghierarchydestination] AS WD  
    JOIN [dbo].[CustomerDestination] AS cd ON wd.Region = cd.[Region]  
    JOIN [dbo].[PersonalInfoDestination] AS Pd ON cd.[Customer ID] = Pd.[CustomerID]  
    JOIN [dbo].[orderdetailsDestinations] AS Od ON pd.[CustomerID] = Od.[customer Id]  
    JOIN [dbo].[Dimproduct] AS Dp ON od.[product Id] = Dp.ProductID  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

5 additional answers

Sort by: Most helpful
  1. Viorel 110.1K Reputation points
    2020-10-04T06:38:27.033+00:00

    Remove SELECT TOP 1 * and try again.

    1 person found this answer helpful.
    0 comments No comments

  2. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-10-04T14:08:58.62+00:00

    Does the SELECT work aloe, without the INSERT? If not, please post the exact error message.

    1 person found this answer helpful.

  3. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-10-04T15:31:53.717+00:00

    So the problem isn't with the insert, it is with the select? And you don't get the error messages that you posted initially?

    If the select return 0 rows, then troubleshoot that. 0 rows doesn't produce that error you posted initially, ie when used in an insert.

    1 person found this answer helpful.
    0 comments No comments

  4. MelissaMa-MSFT 24,176 Reputation points
    2020-10-06T01:25:06.587+00:00

    Hi @Laura Ijewere ,

    Please check whether there is any output after running below query:

    SELECT top 1 [Row Id]  
     ,od.[order id]  
     ,od.[Order Date]  
     ,od.[Date] AS ShipDate  
     ,od.[Ship Mode]  
     ,od.[customer Id]  
     ,cd.[Customer Name]  
     ,pd.[Gender]  
     ,pd.[MaritalStatus]  
     ,pd.[Age]  
     ,pd.[Numberofchildren]  
     ,cd.[Segment]  
     ,cd.[Country]  
     ,cd.[City]  
     ,cd.[State]  
     ,cd.[Postal Code]  
     ,od.[Region]  
     ,wd.[EmployeeID]  
     ,wd.[Person]  
     ,wd.[Type]  
     ,wd.[Weekly]  
     ,dp.[ProductID]  
     ,dp.[Category]  
     ,dp.[SubCategory]  
     ,dp.[ProductName]  
     ,od.[sales]  
     ,od.[Quantity]  
     ,od.[Discount]  
     FROM [dbo].[weeklyorghierarchydestination] AS WD  
     JOIN [dbo].[CustomerDestination] AS cd ON wd.Region = cd.[Region]  
     JOIN [dbo].[PersonalInfoDestination] AS Pd ON cd.[Customer ID] = Pd.[CustomerID]  
     JOIN [dbo].[orderdetailsDestinations] AS Od ON pd.[CustomerID] = Od.[customer Id]  
     JOIN [dbo].[Dimproduct] AS Dp ON od.[product Id] = Dp.ProductID  
    

    If there is no output, then you need to check the cause. Any data issue? Multiple tables join issue? Any other issue?

    Please provide the error message if possible and we could check further.

    In your situation , it could be better for you to you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.