please why is the query not working

Laura Ijewere 41 Reputation points
2020-10-04T00:01:39.633+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,516 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Dan Guzman 9,101 Reputation points
    2020-10-04T09:02:49.127+00:00

    The query includes an extraneous SELECT TOP 1 * so simply remove that line to insert all the results.

    If your intent is to limit the rows inserted, add the TOP clause to the other SELECT like below. Be aware that you should to add an ORDER BY clause to the end of the query for deterministic results. Otherwise, you may insert different rows when the same query is run with the same data.

    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
    ORDER BY [Row Id]; --recommended with TOP
    
    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2020-10-05T04:41:55.363+00:00

    Hi @Laura Ijewere ,

    We found that there was another similar thread posted by you.

    You could consider to delete one of them if necessary to avoid duplicate posts.

    Please try with the method mentioned by Dan.

    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.

    0 comments No comments