Data Import and Replace

Sometimes there are situations when you want to ignore the current primary key/unique value when importing into a table.

Some databases have syntax constructs in addition to the INSERT INTO that may help.

The choice of the replacement method is in the import settings - in "Data load settings".

By default, the selection is <None>, you can select other options from the drop-down list. The options available depend on the target database you are importing to.

The database can only support the replace method or the ignore method. In this case, the list of methods will consist of only one item except <None>. If the base does not support the replacement methods, or if we have not added an implementation yet, then the combo with the list will be disabled.

Further, you will find a list of databases supporting these methods and examples of syntax.

Let's take a look at an example of how this works. We use a small, simple, slightly-modified Sakila (MySQL) table - sakila.language

CREATE TABLE language_insert (
language_id tinyint unsigned NOT NULL,
name char(20) NOT NULL,
last_update timestamp NOT NULL,
PRIMARY KEY (language_id)
); 

INSERT INTO sakila.language_insert (language_id,name,last_update) VALUES
(1,'English','2006-02-15 05:02:19.0'),
(2,'Italian','2006-02-15 05:02:19.0'),
(3,'Japanese','2006-02-15 05:02:19.0'); 

If we try to execute this request twice, we will get the following error: SQL Error [1062] [23000]: Duplicate entry '1' for key 'language_insert.PRIMARY' (This message may look different in other databases).

Let's take a new .csv file with the following content and try to use the replace methods.

"language_id","name","last_update"
1,Spanish,"2020-04-20 05:02:19.0"
2,Russian,"2020-05-20 05:02:19.0"
3,Belgian,"2020-06-20 05:02:19.0"
4,Mandarin,"2006-02-15 05:02:19.0"
5,French,"2006-02-15 05:02:19.0"
6,German,"2006-02-15 05:02:19.0"

If we set the "INSERT IGNORE" method in the settings, the result of the insert will look like this:

There will be no insertion errors, the first three lines will not change, and the fourth to sixth lines will be added to the table.

If we set the "REPLACE INTO" method in the settings, the result of the insert will look like this:

There will be no insertion errors, the first three lines will be replaced and the fourth to sixth lines will be added to the table.

INSERT IGNORE and REPLACE INTO

Insert examples:

"INSERT IGNORE"

INSERT IGNORE INTO language_insert(language_id, name, last_update) 
VALUES(1, 'English', '2006-02-15 05:02:19.0');

"REPLACE INTO"

REPLACE INTO language_insert(language_id, name, last_update) 
VALUES(1, 'English', '2006-02-15 05:02:19.0');

Documentation
"INSERT OR IGNORE" and "INSERT OR REPLACE"

Insert examples:

"INSERT OR IGNORE"

INSERT OR IGNORE INTO language_insert(language_id, name, last_update) 
VALUES(1, 'English', '2006-02-15 05:02:19.0');

"INSERT OR REPLACE"

INSERT OR REPLACE INTO language_insert(language_id, name, last_update) 
VALUES(1, 'English', '2006-02-15 05:02:19.0');

Available for PostgreSQL version 9.5.
"ON CONFLICT DO NOTHING" and "ON CONFLICT DO UPDATE SET"

Insert examples:

"ON CONFLICT DO NOTHING"

INSERT INTO language_insert(language_id, name, last_update) 
VALUES(1, 'English', '2006-02-15 05:02:19.0') ON CONFLICT DO NOTHING;

"ON CONFLICT DO UPDATE SET"

INSERT INTO language_insert(language_id, name, last_update) 
VALUES(1, 'English', '2006-02-15 05:02:19.0') 
ON CONFLICT (language_id) 
DO UPDATE SET (language_id, name, last_update) = (EXCLUDED.language_id, EXCLUDED.name, EXCLUDED.last_update);

Available for FireBird version 2.1.
"UPDATE OR INSERT INTO"

Insert examples:

"UPDATE OR INSERT INTO"

UPDATE OR INSERT INTO language_insert(language_id, name, last_update) 
VALUES(1, 'English', '2006-02-15 05:02:19.0');

Available for Oracle version 11.2.
"INSERT IGNORE ROW INDEX"

Insert examples:

"INSERT IGNORE ROW INDEX"

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(LANGUAGE_INSERT, LANGUAGE_INSERT_PK) */ 
INTO LANGUAGE_INSERT(LANGUAGE_ID, NAME, LAST_UPDATE) VALUES(1, 'English', TIMESTAMP '2006-02-15 05:02:19.0');