please why is the query not working

Question

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

Answer 1

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

Answer 2

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.