0

I'm trying to merge data from multiple tables in different databases on the same server however the result is incorrect (i.e. the number of rows returned is more than what it should be). Here's the code:

merge into factOrders fo
using (
    select CustomerKey
        ,ProductKey
        ,CategoryKey
        ,dt1.TimeKey as [OrderDateKey]
        ,dt2.TimeKey as [RequiredDateKey]
        ,dt3.TimeKey as [ShippedDateKey]
        ,o.OrderID
        ,Freight
        ,od.UnitPrice as [UnitPrice]
        ,od.Quantity as [Quantity]
        ,od.Discount as [Discount]
        ,od.UnitPrice * od.Quantity * (1 - Discount) as [TotalPrice]
        ,sh.CompanyName as [ShipperName]
        ,sh.Phone as [ShipperPhone]
    from db1.dbo.Orders o
        ,db1.dbo.[Order Details] od
        ,db1.dbo.Shippers sh
        ,db1.dbo.Products p
        ,dimCustomers dc
        ,dimProducts dp
        ,dimCategories dct
        ,dimTime dt1
        ,dimTime dt2
        ,dimTime dt3
    where o.OrderID = od.OrderID
        and dc.CustomerID = o.CustomerID
        and dp.ProductID = od.ProductID
        and dct.CategoryID = p.CategoryID
        and sh.ShipperID = o.ShipVia
        and dt1.date = o.OrderDate
        and dt2.date = o.RequiredDate
        and dt3.date = o.ShippedDate
    ) o
    on (
            fo.ProductKey = o.ProductKey
            and fo.CustomerKey = o.CustomerKey
            and fo.CategoryKey = o.CategoryKey
            and fo.OrderDateKey = o.OrderDateKey
            )
when matched
    then
        update
        set......
when not matched
    then
        insert.....
        values (......)

I can tell the issue is somewhere in the WHERE condition and I've tried multiple options but not getting the correct result.

1

You don't mention what engine this is going against so I'm assuming it's Microsoft SQL Server. One word of advice is that you put the join predicate and specify the type of join in the FROM clause, it will make errors like this much more visible as well as making reading the query easier. You forgot to specify the join type for the db1.dbo.Products table, which I think makes it a cross join, so vastly multiplying the number of rows you would get back. See below.

merge into factOrders fo
using (
    select CustomerKey
        ,ProductKey
        ,CategoryKey
        ,dt1.TimeKey as [OrderDateKey]
        ,dt2.TimeKey as [RequiredDateKey]
        ,dt3.TimeKey as [ShippedDateKey]
        ,o.OrderID
        ,Freight
        ,od.UnitPrice as [UnitPrice]
        ,od.Quantity as [Quantity]
        ,od.Discount as [Discount]
        ,od.UnitPrice * od.Quantity * (1 - Discount) as [TotalPrice]
        ,sh.CompanyName as [ShipperName]
        ,sh.Phone as [ShipperPhone]
    from db1.dbo.Orders o
        INNER JOIN db1.dbo.[Order Details] od ON o.OrderID = od.OrderID
        INNER JOIN db1.dbo.Shippers sh ON sh.ShipperID = o.ShipVia
        INNER JOIN dimCustomers dc ON dc.CustomerID = o.CustomerID
        INNER JOIN dimProducts dp ON dp.ProductID = od.ProductID
        INNER JOIN dimCategories dct ON dct.CategoryID = p.CategoryID
        INNER JOIN db1.dbo.Products p ON 
        INNER JOIN dimTime dt1 ON dt1.date = o.OrderDate
        INNER JOIN dimTime dt2 ON dt2.date = o.RequiredDate
        INNER JOIN dimTime dt3 ON dt3.date = o.ShippedDate
    ) o
    on (
            fo.ProductKey = o.ProductKey
            and fo.CustomerKey = o.CustomerKey
            and fo.CategoryKey = o.CategoryKey
            and fo.OrderDateKey = o.OrderDateKey
            )
|improve this answer|||||
  • Thank you. Yes I'm using MS SQL Server. I'm quite new to SQL and learning on my own so don't have too much knowledge about joins. If I specify the join for db1.dbo.Products as (INNER JOIN db1.dbo.Products p ON p.ProductID=od.ProductID), the number of rows returned is way too less. The expected output should be the sum of number of rows in orders and order details tables. Not sure what am I missing? – Sean Oct 27 '15 at 7:14
  • @Sean, I'm willing to bet that you have NULLS for some of the values that you are hanging additional tables off of. For example, the o.ShippedDate is likely going to be NULL for any orders that haven't shipped yet. In that case, change the join type to a LEFT OUTER. When I'm trying to figure this kind of stuff out and I don't have a good/accurate data model to work off of, I typically reduce the query to it's basics. In this case, just the order and order details table to verify my numbers. Then add each additional table one at a time until I identify the issue. – Jonathan Fite Oct 27 '15 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.