Sql-server – How to use a view to span two tables that are identical

sql servertableview

We have table A that receives data from our shop floor and we currently have it so that the data is moved from table A to table B after 5 years. I want to streamline this but need help creating a view. I can change the stored procedure to move the data after one month, but need to be able to point to a view with the combined data of table A and B. I tried the below but get the following error: "Column name 'PARTNOAUX' in view or function 'Table_Full_' is specified more than once."

CREATE VIEW Table_Full AS
SELECT *
FROM [A].[dbo].[AUX] AS S
Inner Join [B].[dbo].[AUX]AS T
ON S.DATETIMEAUX=T.DATETIMEAUX;

Best Answer

You'll need to use a union instead. If your data is indeed distinct, use a union all for better performance -- the step to eliminate duplicates is eliminated.

create view table_full as
select *
from [A].[dbo].[AUX]
union all
select *
from [B].[dbo].[AUX];
Related Question