June 22, 2011 at 2:06 am
Hi all
I have not been wokring much with these to features but I do belive I now have to use them.
The case is that I have one live table and one history table.
They are equal in respect of fields.
I want to create a new table where I get all data from live and all from history that does not exist in live.
Tried something like
SELECT
T1.[Item],
T1.[Warehouse],
T1.[year],
T1.[week],
T1.[startdate],
T1.[stopdate],
T1.[QTY]
FROM
live_base2 as T1
UNION ALL
SELECT
T2.[Item],
T2.[Warehouse],
T2.[year],
T2.[week],
T2.[startdate],
T2.[stopdate],
T2.[QTY]
FROM
history_base2 as T2
WHERE NOT EXISTS
(SELECT * from live_BASE2 AS T3 where T2.item = t3.item and t2.warehouse = t3.warehouse and t2.year = t3.year and t2.week = t3.week)
Am I way of here?
Any comments?
Dan
June 22, 2011 at 4:31 am
Your code looks as though it will work although I would also be inclined to try an outer join to see if it is more efficient.
Soemthing like:
SELECT
COALESCE(T1.Item, T2.Item) AS Item
,COALESCE(T1.Warehouse, T2.Warehouse) AS Warehouse
,COALESCE(T1.[year], T2.[year]) AS [year]
,COALESCE(T1.[week], T2.[week]) AS [week]
,COALESCE(T1.startdate, T2.startdate) AS startdate
,COALESCE(T1.stopdate, T2.stopdate) AS stopdate
,COALESCE(T1.QTY, T2.QTY) AS QTY
FROM live_base2 AS T1
RIGHT JOIN history_base2 AS T2
ON T1.item = T2.item
AND T1.warehouse = T2.warehouse
AND T1.[year] = T2.[year]
AND T1.[week] = T2.[week]
June 23, 2011 at 12:56 am
Thanks Ken.
Your code works fine. Tried both to test speed, but the I have to small amount of data to get any good reading.
Dan
June 27, 2011 at 3:05 am
Hello Dan,
you don't need any NOT EXISTS clause (nor any OUTER JOIN clause) because UNION will remove any duplicate rows. So if there is a row that belongs to both your live table and your history table that row will appear once in the result query, not twice.
If you want to obtain duplicate rows use UNION ALL instead of UNION.
Regards,
Francesc
June 27, 2011 at 3:09 am
Of course, UNION will remove duplicate rows but not different rows with the same key. Maybe you have rows within bot your live table and your history table with the same key but with different values.
Francesc
June 27, 2011 at 3:09 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply