user1736700 user1736700 - 1 month ago 4
SQL Question

Union 2 tables have same structure SQL Server

I have 2 tables with same structure ex:

Table1(Timestamp, Value)
and
Table2(Timestamp, Value)
. I want union 2 these tables as a table like:
Table3(Timestamp, Value1, Value2)
where
Value1 is value of Table1
and
Value2 is value of Table2
. How can I do that on SQL Server?

Answer

Try this

DECLARE @table1 TABLE (TS int, Value int);
DECLARE @table2 TABLE (TS int, Value int);

DECLARE @table3 TABLE(TS int, Value1 int, Value2 int);


INSERT INTO @table1(TS, Value)
SELECT 1, 12
UNION SELECT 3, 34
UNION SELECT 9, 122

INSERT INTO @table2(TS, Value)
SELECT 1, 23
UNION SELECT 2, 99
UNION SELECT 3, 999
UNION SELECT 5, 1222

-- ANSWER: --

INSERT INTO @table3(TS, Value1, Value2)
SELECT ISNULL(t1.TS, t2.TS), t1.Value, T2.Value
FROM @table1 t1
FULL JOIN @table2 t2 ON t2.TS = t1.TS

SELECT * FROM @table3
ORDER BY TS

Example code above. Change TS to preferred name and data type