CODEWITHSUNDEEP
mysqlsqlmerge
Michel R.
Michel R.

Reputation: 25

Merging 2 tables in MySQL & autofill missing columns

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

Upvotes: 1

Views: 1477

Answers (1)

Ron Smith
Ron Smith

Reputation: 3266

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

Upvotes: 2

Related Questions