Databases
StackOverflow2013
Postsdbo
POIs there a way to ignore columns that don't exist on INSERT?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there a way to ignore columns that don't exist on INSERT?
    text
    copied!<p>I'm using a <a href="http://dev.mysql.com/doc/query-browser/en/">MySQL GUI</a> to migrate some sites to a new version of a CMS by selecting certain tables and running the <code>INSERT</code> 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:</p> <blockquote> <p>Script line: 1 Unknown column 'user_id' in 'field list'</p> </blockquote> <p>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 <code>INSERT IGNORE</code>, but this seems to be for ignoring duplicate keys (not what I'm looking for).</p> <p>Is there any way to preform an <code>INSERT</code> while ignoring columns that don't exist in the target table? I'm looking for something "painless", like some existing SQL functionality. </p> <p>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:</p> <pre><code>-- 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, '', ''); </code></pre> <p>If something like this simply doesn't exist, I'm happy to accept that as an answer and continue to use my current workaround.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload