CODEWITHSUNDEEP
mysqlsql
user1032911
user1032911

Reputation: 3

MySQL - Copying table data to non-identical table

Let's say I have two tables of similar structure, but one is empty and the other has a few rows of information:

table1

col1 | col2 | col3
red  | cow  | 1
blue | dog  | 2
gray | pig  | 3

table2

col1 | col2 | col3 | col4

table3

col1 | col2

Attempting:

insert into `table2` select * from `table1`

will not work because of the unmatched column count, and the same is true substituting table3 for table2.

Altering the * portion of the SELECT statement is not an option for dynamic purposes. So a workaround would have to be a modified SELECT combining the information.

Is there a JOIN statement or something that would merge the structures of the tables and the data so it would look like this:

select * from `table1`,`table2` (JOIN or some other statement)
col1 | col2 | col3 | col4
red  | cow  | 1    | NULL
blue | dog  | 2    | NULL
gray | pig  | 3    | NULL

select * from `table1`,`table3` (JOIN or some other statement)
col1 | col2
red  | cow
blue | dog
gray | pig

Basically just merging any columns with identical names and nullifying outside matches. Again, it can't refer to specific column names to keep things dynamic. It seems doable, but I'm finding it impossible to find the answer.

Thanks a ton to anybody that can help.

Upvotes: 0

Views: 485

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659197

If the target table of the INSERT is a subset of the source table like in your example, then there is a simple solution:

insert into table2(col1, col2, col3)
select *
from   table1;

Or, if the first columns of both tables are in the same order like in your example, then you your posted query should actually work:

insert into table2 select * from table1;

I quote the manual for version 5.6 on that:

If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 10.1.4, “Data Type Default Values”. See also Section 1.8.6.2, “Constraints on Invalid Data”.

However, it is almost always a bad idea to depend on "SELECT *" in an INSERT operation. So, in spite of your request, it really should be:

insert into table2(col1, col2, col3)
select col1, col2, col3
from   table1;

For anything more you'll have to use DESCRIBE tablex and build your queries dynamically.

Upvotes: 1

Xedecimal
Xedecimal

Reputation: 3223

You could allow null values for the columns that will end up being excluded.

insert into `table2` (col1, col2, col3) select col1, col2, col3 from `table1`

may also be of use to you.

Upvotes: 0

Related Questions