importing sql and ignore/replace

[gerardg]

I have a rather lengthy sql file with a bunch of inserts. Is there some way to specify ignore/replace when duplicated are encountered (like you can with some queries)?

[[deleted]]

SQL can only refuse to insert a record if there is a key or constraint telling it that the new data is not valid.

If your tables have primary keys and your SQL file also inserts those values, then the database will refuse to insert duplicate values for the primary key.

If not, then SQL has no way of telling the difference between a record that is being inserted twice, or two records that just happen to have the same content (it happens).

[terje-s]

Like vincent says, SQL cannot check this alone.

However you could write a perl script (or php, python, c, c++, whatever) that reads the content of the sql files, performs a set of SELECTS and determines if the data already exists. (And ofcourse, create a new sql file with only the updated statements).

Such an operation, however, requires that your data is "clean". Before starting this job, make sure you know this is really what you want to do. (i.e. how would duplicate records damage your data? is it more important to keep the old ones than the new ones? (or vice versa) etc.)

[[deleted]]

Actually, SQL can check this alone, but it cannot tell wether you are inserting a duplicate or inserting a record with the same data.

You can 'easily' add a unique index on all tables combined, that will block inserts of duplicate data. But, that would also block inserts of records that just happen to have the same content.

There's the real problem: how can you tell teh diff between an intentional duplicate and an unintential duplicate?

[gerardg]

Yeah. I was just wondering if there was an option when importing sql to either ignore or replace duplicate entries, like you can with mysqlimport and text files. Either that, or keep executing the script if errors are encountered (ie. go to the next sql statement) - can't remember if I saw an option for something like this in the docs.

Then there's always plan C: add 'ignore' to all the insert statements; and plan D: empty the data in the tables.