Merging 2 tables in MySQL & autofill missing columns

Question

I have a little problem, I would like to merge 2 tables (not all the columns are the same), and to put NULL where the columns are different...

It's quite hard to explain this with words, so here's an example of what I'm trying to do :

If I had this table :

+------+------+------+------+
| col1 | col2 | col3 | type |
+------+------+------+------+
| 1    | NULL | 1    | A    |
| NULL | 1    | NULL | A    |
+------+------+------+------+

And this table :

+------+------+------+------+
| col2 | col3 | col4 | type |
+------+------+------+------+
| 1    | NULL | 1    | B    |
| NULL | 1    | NULL | B    |
+------+------+------+------+

I would like to create a table like this one :

+------+------+------+------+------+
| col1 | col2 | col3 | col4 | type |
+------+------+------+------+------+
| 1    | NULL | 1    | NULL | A    |
| NULL | 1    | NULL | NULL | A    |
| NULL | 1    | NULL | 1    | B    |
| NULL | NULL | 1    | NULL | B    |
+------+------+------+------+------+

Is it possible ? ^.^

Edit : In the real tables, I have around 40 columns for the first table and 80 for the second one

How many columns overlap between the two tables (like `col2` in your example)? — Gordon Linoff, Mar 27 '14 at 00:42

Ron Smith · Accepted Answer · 2014-03-27T00:19:07.593

2

Sure, just Union the record sets together with nulls in the columns that don't exist in each table:

insert into YourMergeTable
select col1,col2,col3,null col4,type from YourFirstTable
union all
select null col1,col2,col3,col4,type from YourSecondTable

http://www.sqlfiddle.com/#!2/16110/1

edited Mar 27 '14 at 00:19

answered Mar 27 '14 at 00:13

Ron Smith

3,241
1
13
16

It works, yes, but the problem is that I have around 40 columns for the first table and 80 for the second xD I could write them all, but it may be possible to have a query that would automatically create (and fill with NULL) the columns that aren't in one of the table ? – Michel R. Mar 27 '14 at 00:30
I suppose you could do a prepared statement using the schema tables as a source, but I would only do that if you will need to do this repeatedly with changing tables. Here is a link showing how to do prepared statements with dynamic columns: http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure – Ron Smith Mar 27 '14 at 00:52
Yes, I thought about using prepared statements [something like @sql = select concat (*"query"*, group_concat(column_name), *"query"*) from INFORMATION_SCHEMA.COLUMNS] but I don't know how to add the 'null AS missing_columns' in the statement ^^' – Michel R. Mar 27 '14 at 10:59
1) Get a distinct list of names from the two tables. 2) Create your Merge table with that list. 3) Using the Column names from the Information Schema, insert the records from each original table separately. If you specify which columns you are inserting data into, the unspecified columns will remain null. – Ron Smith Mar 27 '14 at 21:16
updated sqlfiddle: http://www.sqlfiddle.com/#!2/ecd9f/1 I am hard coding the columns, but you could use the Information Schema to build those queries. – Ron Smith Mar 27 '14 at 21:21

Merging 2 tables in MySQL & autofill missing columns

1 Answers1