Say, I have 6 temp tables stored as the following (those 3 are samples) and I would to form them into 1 single table, to be in series (headers Date, Com, Price).
Com A
Date Price
2015-05-01 00:00:00.000 34.25
2015-05-02 00:00:00.000 35.20
2015-05-03 00:00:00.000 36.70
2015-05-04 00:00:00.000 32.37
2015-05-05 00:00:00.000 32.40
2015-05-06 00:00:00.000 32.20
Com B
Date Price
2015-05-07 00:00:00.000 54.29
2015-05-08 00:00:00.000 54.50
2015-05-09 00:00:00.000 56.21
2015-05-10 00:00:00.000 56.70
2015-05-11 00:00:00.000 58.20
Com C
Date Price
2015-05-12 00:00:00.000 34.29
2015-05-13 00:00:00.000 24.50
2015-05-14 00:00:00.000 76.21
2015-05-15 00:00:00.000 36.70
2015-05-16 00:00:00.000 48.20
The output to look like, and I would like to store it as another temp table for merging later:
Date Com Price
2015-05-01 00:00:00.000 A 34.25
2015-05-02 00:00:00.000 A 35.20
2015-05-03 00:00:00.000 A 36.70
2015-05-04 00:00:00.000 A 32.37
2015-05-05 00:00:00.000 A 32.40
2015-05-06 00:00:00.000 A 32.20
2015-05-07 00:00:00.000 B 54.29
2015-05-08 00:00:00.000 B 54.50
2015-05-09 00:00:00.000 B 56.21
2015-05-10 00:00:00.000 B 56.70
2015-05-11 00:00:00.000 B 58.20
2015-05-12 00:00:00.000 C 34.29
2015-05-13 00:00:00.000 C 24.50
2015-05-14 00:00:00.000 C 76.21
2015-05-15 00:00:00.000 C 36.70
2015-05-16 00:00:00.000 C 48.20
Seems like a simple union all to me:
SELECT [Date], 'A' as Com, Price
FROM [Com A]
UNION ALL
SELECT [Date], 'B' as Com, Price
FROM [Com B]
UNION ALL
SELECT [Date], 'C' as Com, Price
FROM [Com C]