I have a large number of email addresses I'm importing from an old mailing list system to a new one.

The destination table one has a primary key across two columns (the mailing list ID and the email address), so that no one person can be subscribed to the same list twice.

I started by creating a long SQL query as follows:

INSERT INTO lz_maillist_subscription (
                email,
                listId,
                subscribeDate,
                firstName,
                lastName,
                htmlEmail )
          VALUES (
                    'john@doe.com',
                    '1',
                    '1042254369',
                    'John',
                    'Doe',
                    '0',
                    ) ,
(
                    'fred@flintstone.com',
                    '1',
                    '1042277774',
                    'Fred',
                    'Flintstone',
                    '0',
                    ) ,
....

I discovered that if I run this in from PHP using mysql_query(), mysql hits an error (e.g. a duplicate entry in the primary key columns), mysql throws the error and stops. Any rows after the offending row are not inserted, but the ones before it remain.

My workaround is to export this to a .sql file, spreading the rows across multiple INSERT statements:

INSERT INTO lz_maillist_subscription...;
INSERT INTO lz_maillist_subscription...;
INSERT INTO lz_maillist_subscription...;

... and then feed this file to mysql on the command line with the -f option.

Is this going to work, or will the -f option actually create a duplicate primary key?

-Antun

    I would turn of the UNIQUE value during the import, print out the offending rows ("SELECT FROM lz_maillist_subscrip GROUP BY email HAVING COUNT()>1"), manually fix the offending rows, then turn UNIQUE back on.

      Thanks - actually I found another way of achieving what I wanted:

      I did an insert from PHP, but added the IGNORE flag (INSERT IGNORE INTO...).

      -Antun

        Write a Reply...