1

I have a MySQL table with data in it, called current. I import new data into a table called temp. Both these tables have auto_increment ID columns.

The table structure is not known in advance for the data import (there are various file structures that I need to import), event though the structure of current and temp will be the same.

Because of the unknown column configuration of the import files (tables created on the fly for each different file configuration), I cannot select specific columns, hence I would have to select all columns, less the ID column from table temp and import the result into table current.

I need to import into temp first, as the files can be large, and I need to do processing on the data before saving into the database, so I do not want to do any operations on the current table before I have imported the separate file first.

The ID column from the temp table prevents the insert into the current table due to duplicate key.

So I need something like this:

INSERT INTO `current`
(SELECT **ALL COLUMNS EXCEPT ID** FROM `temp`)

Any ideas on how to write the section ALL COLUMNS EXCEPT ID? Is this even possible?

user2864740
  • 60,010
  • 15
  • 145
  • 220
Kobus Myburgh
  • 1,114
  • 1
  • 17
  • 46

1 Answers1

3

There's no * except foo. You'll have to list all of the columns, except the ones you don't want.

SELECT field1, field2, ..., fieldN ...

You could do it via dynamic scripting, e.g. query information_schema for the field names, build up the field list as a string, prepare that string as query, execute it, etc...

Marc B
  • 356,200
  • 43
  • 426
  • 500