From: Michael Stassen (Michael.Stassenverizon.net)
Date: Mon Oct 03 2005 - 10:58:04 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Gerhard Prilmeier wrote:
> Hello,
>
> I use tables with one primary key (which is the only unique key). I'd
> like to export data from such a table to a file, and then import it on
> another machine.
> If a duplicate unique key is found when importing with LOAD DATA INFILE,

How does that happen? I take it you are adding the imported data into an
already populated table.

> MySQL gives me the choice of whether to
> 1. stop execution with an error
> 2. not import rows with duplicate unique keys (using IGNORE)

It can also replace the existing rows, but that's not what you want.

> What I'd like to do is to alter the unique key (either the imported or
> the existing one) to a value that does not already exist, and then
> import the row.

Don't alter the keys for the existing data! That path leads to trouble.
Usually, other tables will refer to rows in this table by key. Changing
keys breaks relationships.

Is it the case that the imported data is simply a set of new rows with no
references to it? If so, there's no reason to preserve the old key for any
of the imported rows. Instead, we just assign new keys to all the imported
rows. This should be relatively easy if the primary key on the destination
table is AUTO_INCREMENT. In that case, the simplest solution would be to
not export the keys in the first place. Then new keys will be assigned
automatically when you leave out the key column during the import into the
destination table. Somethng like

   SELECT col1, col2, ...
   INTO OUTFILE '/tmp/export.txt'
   FROM export_table;

   LOAD DATA INFILE '/tmp/export.txt'
   INTO TABLE dest_table
   (col1, col2, ...);

where "col1, col2, ..." is all the columns except the key, or

   SELECT NULL, col1, col2, ...
   INTO OUTFILE '/tmp/export.txt'
   FROM export_table;

   LOAD DATA INFILE '/tmp/export.txt'
   INTO TABLE dest_table;

where "NULL" is in the position of the key column.

> Do I have to fall back on a bunch of INSERT statements to accomplish
> this, or do you see a way to get there with LOAD DATA INFILE?

If you already have the exported data and don't want to start over, you can
probably accomplish the same thing with a temporary table. Something like

   # make a temporary table to match dest_table
   CREATE TEMPORARY TABLE expdata SELECT * FROM dest_table WHERE 0;

   # change the temp table to allow NULLs in the key column
   ALTER TABLE expdata CHANGE id id INT;

   # import the data int the temp table
   LOAD DATA INFILE '/tmp/export.txt'
   INTO TABLE expdata;

   # change the key column to all NULLs
   UPDATE expdata SET id = NULL;

   # copy the temp table rows into dest_table, where new auto_inc
   # keys will replace the NULLs in the imported key column
   INSERT INTO dest_table SELECT * FROM expdata;

   # clean up
   DROP TABLE expdata;

> Thank you very much!
> Gerhard Prilmeier

If this isn't what you need, I think we'll need more details about your
tables and what you are trying to accomplish.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]