INSERT from SELECT statement, column count-CodePudding

Table1 has one column and table2 has three columns. The names are all unique.

INSERT INTO table2 (SELECT * FROM table1 WHERE name = 'Brian')

#1136 - Column count doesn't match value count at row 1

What is the easiest way to append NULL or empty strings to the results of the SELECT query?

I have tried this and many other variations:

INSERT INTO table2 (SELECT * FROM test WHERE name = 'Brian', '','') 
INSERT INTO test2 ((SELECT * FROM test WHERE name = 'Brian')   '' , '')

CodePudding user response：

The easiest way is to specify the column names in both source and target tables:

INSERT INTO table2 (col2) -- change col2 to the name of the column in table2 that will receive the values
SELECT col1 -- change col1 to the name of the column in table1
FROM table1 
WHERE name = 'Brian';

All other than col2 columns in table2 will be set to null or their default values (if defined in the table's definition).

Even if col1 is the only column in table1, you should prefer SELECT col1 over SELECT *, because if you add any columns to the table SELECT * would stop working.