选择插入语句返回1000个字段，而不是10个字段

Question

当前，我在SQL Server中有此代码，该代码应该从数据库中获取值并将其放入另一个数据库中。

Insert into AppleFinalX.[dbo].SalesFactTable(SalesType, TotalSales, SalesHDRDate, ProductSales, ProductID) 
SELECT AppleFinal.[dbo].SalesHDRTable.SalesType,
       AppleFinal.[dbo].SalesHDRTable.TotalSales,
       AppleFinal.[dbo].SalesHDRTable.SalesHDRDate,
       AppleFinal.[dbo].SalesDetailsTable.PurchaseAmount,
       AppleFinal.[dbo].ProductTable.ProductID
FROM AppleFinal.[dbo].SalesHDRTable, 
     AppleFinal.[dbo].SalesDetailsTable, 
     AppleFinal.[dbo].ProductTable 

基本上，它从不同的数据库(AppleFinal(中获取三个表的值，并将它们放在不同数据库(AppleFinalx(的表中。但是不幸的是，它返回1000个记录而不是10。有人知道为什么会发生这种情况吗？:(所有桌子都有10个记录，所以我很震惊地看到新表中的1000个记录...

Answer 1

正如其他人所评论的那样，您将每个表的每一行连接到其他表的每一行。这显然不是您想要的。使用联接命令是解决方案。这样的东西：

SELECT AppleFinal.[dbo].SalesHDRTable.SalesType,
   AppleFinal.[dbo].SalesHDRTable.TotalSales,
   AppleFinal.[dbo].SalesHDRTable.SalesHDRDate,
   AppleFinal.[dbo].SalesDetailsTable.PurchaseAmount,
   AppleFinal.[dbo].ProductTable.ProductID
FROM AppleFinal.[dbo].SalesHDRTable
    INNER JOIN AppleFinal.[dbo].SalesDetailsTable
        ON  AppleFinal.[dbo].SalesHDRTable.ID = AppleFinal.[dbo].SalesDetailsTable.SalesID
    INNER JOIN AppleFinal.[dbo].ProductTable
        ON AppleFinal.[dbo].SalesDetailsTable.ProductID = AppleFinal.[dbo].ProductTable.ID

Answer 2

明确说明您的加入更好：

INSERT INTO AppleFinalX.dbo.SalesFactTable
            (SalesType, TotalSales, SalesHDRDate, ProductSales, ProductID) 
SELECT sh.SalesType,
       sh.TotalSales,
       sh.SalesHDRDate,
       sd.PurchaseAmount,
       pt.ProductID
FROM AppleFinal.dbo.SalesHDRTable  sh
    LEFT JOIN   AppleFinal.dbo.SalesDetailsTable sd ON  sd.saleType = sh.saleType
    LEFT JOIN   AppleFinal.dbo.ProductTable pt      ON pt.ProductID = sh.ProductID

Answer 3

，尽管我希望应用必要的加入，但请尝试以下操作：

Insert into AppleFinalX.[dbo].SalesFactTable(SalesType, TotalSales, SalesHDRDate, ProductSales, ProductID) 
SELECT AppleFinal.[dbo].SalesHDRTable.SalesType,
       AppleFinal.[dbo].SalesHDRTable.TotalSales,
       AppleFinal.[dbo].SalesHDRTable.SalesHDRDate,
       AppleFinal.[dbo].SalesDetailsTable.PurchaseAmount,
       AppleFinal.[dbo].ProductTable.ProductID
FROM AppleFinal.[dbo].SalesHDRTable, 
     AppleFinal.[dbo].SalesDetailsTable, 
     AppleFinal.[dbo].ProductTable
group by AppleFinal.[dbo].SalesHDRTable.SalesType,
       AppleFinal.[dbo].SalesHDRTable.TotalSales,
       AppleFinal.[dbo].SalesHDRTable.SalesHDRDate,
       AppleFinal.[dbo].SalesDetailsTable.PurchaseAmount,
       AppleFinal.[dbo].ProductTable.ProductID