How is UNION data without duplicates regarding the key?
Summary: I need to combine the recording of the same tables from the database, called over the years, such as prefix_2012
, prefix_2013
, prefix_2014
- without duplicates based on a unique key id
. The third party is identified by the database and I cannot change it.
Can it be defined as a representation?
Details: . A more detailed explanation of what I want is described in the following example. It returns all records with all duplicity.
SELECT
id,
a,
b
FROM prefix_2014.mytable
UNION ALL
SELECT
id,
a,
b
FROM prefix_2013.mytable
UNION ALL
id,
a,
b
FROM prefix_2012.mytable
When replaced UNION ALL
with UNION
only identical records are merged. (This only happens when the values in all columns are the same. Am I right?)
When a new year database is created , all open records are carried over from last year, and the contents of the last year's database are frozen. The transferred records are initially duplicated in the technical sense (same values in all columns). However, the content of the transferred entries with the same id
can be changed later.
Question: Based on unique id
, how can I merge records from all annual databases so that if a record comes from a later year, records from previous years are ignored as duplicates? Can this be done without an explicit loop and without temporary tables? Can this performance be recorded?
source to share
I don't have these tables and data so it might take some work First create a view of IDs and years like this:
CREATE VIEW YearIDs AS
SELECT ID, Max(year) FROM
(SELECT
id, 2014 as year
FROM prefix_2014.mytable
UNION
SELECT
id, 2013
FROM prefix_2013.mytable
UNION
id, 2012
FROM prefix_2012.mytable)
GROUP By ID )
Now the inner join of each choice in your join:
SELECT T1.id, T1.a, T1.b
FROM prefix_2014.mytable AS T1
INNER JOIN YearIDs AS Y1 ON Y1.Id = T1.ID AND Y1.year = 2014
UNION ALL
SELECT T2.id, T2.a, T2.b
FROM prefix_2013.mytable AS T2
INNER JOIN YearIDs AS Y2 ON Y2.Id = T2.ID AND Y2.year = 2013
UNION ALL
SELECT T3.id, T3.a, T3.b
FROM prefix_2012.mytable Y3.Id = T3.ID AND AS T3
INNER JOIN YearIDs AS Y3 ON Y3.year = 2012
source to share
You can use the following:
WITH CTE AS(SELECT
id,
3 as [Year]
FROM prefix_2014.mytable
UNION ALL
SELECT
id,
2 as [Year]
FROM prefix_2013.mytable
UNION ALL
Select
id,
1 as [Year]
FROM prefix_2012.mytable)
Select ID,MAX([Year]) as YR into #T From CTE
group by ID
Select t.ID,a,b From #T t
join Test1 t1
on t1.id = t.id
where YR = 1
UNION ALL
Select t.ID,a,b From #T t
join Test2 t2
on t2.id = t.id
where YR = 2
UNION ALL
Select t.ID,a,b From #T t
join Test3 t3
on t3.id = t.id
where YR = 3
This is an updated version. This is basically a different version of Kell's request
source to share