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.