Create SQL table with contents of two tables with different columns

Question

I have a set of data logs from a machine that I would like to combine into a single table. However older data logs have different columns than newer ones (certain variables were added/tracked later on).

I mocked up an example in Excel that I am trying to mimic in my Microsoft SQL database. I can't determine the query to use.

Thank you for any help!

EDITS: 1. I don't think this is a duplicate. 2. How many data logs? ~100 data log tables. 3. I am using MS SQL 4. I would prefer not to have to type out all the column names if possible, there are many.

Excel Diagram Example

How many different data logs are we talking about? 1, 5, 10, 100? — SQLChao, Jun 21 '17 at 15:19
MySQL and ms sql server are two different products. Which one do you use? — Shadow, Jun 21 '17 at 15:20

score 0 · Answer 1 · answered Jun 21 '17 at 15:23

0

You could use UNION

The query would look like this:

SELECT 
   date, 
   tim, 
   val1, 
   val2, 
   val3 
from dataset1 
union all
SELECT 
   date, 
   tim, 
   val1, 
   '' as val2, 
   val3 
 From dataset2

This will get the data from the 2 tables and null values in the dataset2 table where the column not exist.

answered Jun 21 '17 at 15:23

MagdielAybar

187
1
2
12

Is there a way to do this without listing out all the columns? Some sort of wildcard method? – Hi_Im_E Jun 21 '17 at 15:37
@Hi_Im_E no - you have to identify the columns explicitly if they are different. – D Stanley Jun 21 '17 at 15:38
@DStanley if that is the case is there a query I can run to list out all the columns, comma seperated, so I can copy and paste? – Hi_Im_E Jun 21 '17 at 15:42
@Hi_Im_E if the tables have the same amount of columns you can use select *. If not, you need to list them all. – MagdielAybar Jun 21 '17 at 16:08

Create SQL table with contents of two tables with different columns

1 Answers1