MySQL insert into when number of columns don't match number of values

Question

Hi, I have an insert sql statement, e.g.

INSERT INTO table_a (col_a, col_b, col_c, col_d) VALUES (1, 2, 3, 4), (2, 1, 6, 9), (3, 1, 4, 5)

I want to insert this into another table, however the table I want to insert it into has a different structure to that of the sql statement (it has fewer fields) e.g.

table_b has columns 'col_a', 'col_b', 'col_d'

What do I have to do to the original sql statment so that I can get it to insert it into table_b. I guess it would be something along the lines of just ignoring the value which is in col_c and just sending this to a temp variable rather than a field.e.g.

INSERT INTO table_b (col_a, col_b, @temp_var, col_d) VALUES (1, 2, 3, 4), (2, 1, 6, 9), (3, 1, 4, 5)

Thanks

Answer 1

How about you remove it?

INSERT INTO table_b (col_a, col_b, col_d) VALUES (1, 2, 4), (2, 1, 9), (3, 1, 5)

Answer 2

This is ugly, and I have just tried in in SQLite, but I can image that it also works in MySQL (the documentation doesn't say it's not allowed) (update: see John's comment, it does not work in MySQL):

sqlite> create table t(a,b,c);
sqlite> insert into t (a,b,b,c) values (1,2,3,4);
sqlite> select * from t;
1|2|4

Answer 3

Use a temporary table:

CREATE TEMPORARY TABLE myTemp (
col_a integer,
col_b integer,
col_c integer,
col_d integer
);
INSERT INTO myTemp (col_a, col_b, col_c, col_d) VALUES (1, 2, 3, 4), (2, 1, 6, 9), (3, 1, 4, 5);
INSERT INTO table_a (SELECT col_a,col_b,col_d FROM myTemp);

The table gets dropped once your session ends (or you can remove it manually)