Is there a way to ignore columns that don't exist on INSERT?

Question

I'm using a MySQL GUI to migrate some sites to a new version of a CMS by selecting certain tables and running the INSERT statement generated from a backup dump into an empty table (the new schema). There are a few columns in the old tables that don't exist in the new one, so the script stops with an error like this:

Script line: 1 Unknown column 'user_id' in 'field list'

Cherry-picking the desired columns to export, or editing the dump file would be too tedious and time consuming. To work around this I'm creating the unused columns as the errors are generated, importing the data by running the query, then dropping the unused columns when I'm done with that table. I've looked at INSERT IGNORE, but this seems to be for ignoring duplicate keys (not what I'm looking for).

Is there any way to preform an INSERT while ignoring columns that don't exist in the target table? I'm looking for something "painless", like some existing SQL functionality.

To clarify, I'm working with a bunch of backup files and importing the data to a local database for testing before moving it to the live server. Example of the kind of solution I'm hoping for:

-- Don't try to insert data from columns that don't exist in "new_table"
INSERT INTO `new_table` {IGNORE UNKNOWN COLUMNS} (`id`, `col1`, `col2`) VALUES 
  (1, '', ''),
  (2, '', '');

If something like this simply doesn't exist, I'm happy to accept that as an answer and continue to use my current workaround.

Answer 1

Your current technique seems practical enough. Just one small change.

Rather than waiting for error and then creating columns one by one, you can just export the schema, do a diff and find out all the missing columns in all the tables.

That way it would be less work.

Your gui will be capable of exporting just schema or the following switch on mysqldump will be useful to find out all the missing columns.

mysqldump --no-data -uuser -ppassword --database dbname1 > dbdump1.sql
mysqldump --no-data -uuser -ppassword --database dbname2 > dbdump2.sql

Diffing the dbdump1.sql and dbdump2.sql will give you all the differences in both the databases.

Answer 2

you can write a store function like that:

sf_getcolumns(table_name varchar(100))

return string contatining the filed list like this: 'field_1,field_2,field_3,...'

then create a store procedure

sp_migrite (IN src_db varchar(50), IN target_db varchar(50))

that runs trugh the tables and for each table gets the filed lists and then creates a string like

cmd = 'insert into ' || <target_db><table_name> '(' || <fileds_list> || ') SELECT' || <fileds_list> || ' FROM ' <src_db><table_name>

then execute the string for each table

Answer 3

Is there any way to preform an INSERT while ignoring columns that don't exist in the target table? I'm looking for something "painless", like some existing SQL functionality.

No, there is no "painless" way to do so.

Instead, you must explicitly handle those columns which do not exist in the final tables. For example, you must remove them from the input stream, drop them after the fact, play dirty tricks (engine=BLACKHOLE + triggers to INSERT only what you want to the true target schema), whatever.

Now, this doesn't necessarily need to be manual -- there are tools (as Devart noted) and ways to query the db catalog to determine column names. However, it's not as easy as simply annotating your INSERT statements.

Perhaps the CMS vendor can supply a reasonable migration script?