0

This is a silly little problem. I have a large table with hundreds of columns, so I don't want to write out each column name individually. The problem is that say, table1 is the source table, with 200 columns, and table2 is the destination table with 201 columns, where the last column of table2 is an extra auto-increment (primary key) column. The idea is that I simply can do

insert into table2 select * from table1 where row = ##;

and I would wish that all the data would be copied and the auto-increment column would just do its job. However I get this pesky error message:

Error Code: 1136. Column count doesn't match value count at row 1

Anyone have a simple solution to this?

jeffery_the_wind
  • 17,048
  • 34
  • 98
  • 160
  • 2
    *I don't want to write out each column name individually.* MUST has priority over WANT. – Akina Nov 16 '20 at 16:55
  • The answer is to just give a NULL https://stackoverflow.com/questions/3493612/how-to-insert-new-row-to-database-with-auto-increment-column-without-specifying – jeffery_the_wind Nov 17 '20 at 12:58
  • 1
    *The answer is to just give a NULL* MySQL does not allow `SELECT NULL, * FROM ..`, only `SELECT *, NULL FROM ..`. If "an extra auto-increment (primary key) column" is the last column in `table2`'s structure then this is a solution. – Akina Nov 17 '20 at 13:04
  • @Akina ok I didn't notice that. I luckily am working with the case using `*, NULL` and it is working. – jeffery_the_wind Nov 17 '20 at 14:38

2 Answers2

1

My recommendation is to generate the column names with a SQL query and just cut-and-paste.

But you can also use the temporary table approach:

create table temp_table1 as
    select * from table1 where row = ##;

alter table temp_table1 drop column row;

Then you can use temp_table1 with *. Of course, this assumes that all the other columns line up! I also recommend listing all the columns for the insert . . . and you are back to the recommendation at the beginning of the answer.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

The simplest solution can be create a backup of the table2, drop the autoincrement column then insert whatever to you want to insert as the column number would match, and then you can add the autoincrement column at the end again.

user3315556
  • 175
  • 1
  • 8