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
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
