Geek Answers Handbook

Insert when the number of columns does not match the number of values

I have an sql insert statement, for example.

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 into has a different structure than the structure of the sql operator (it has fewer fields), for example.

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

What do I need to do with the original sql status so that I can insert it into table_b. I assume it will be something like just ignoring the value that is in col_c and just sending it to the temp variable, not the field.eg

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)
+5
source share
3 answers

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 is discarded after the session ends (or you can delete it manually)

+3
source

?

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

This is ugly, and I just tried it in SQLite, but I can imagine that it also works in MySQL (the documentation doesn't say that this is 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
+2
source

More articles:


All Articles