Skip to content

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.

Database-specific methods

The following examples show methods used by some supported databases. The methods available in the On duplicate key list depend on the target database and its driver.

MySQL

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');

Info

Learn more about INSERT IGNORE and REPLACE INTO.

SQLite

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');

Info

Learn more about SQLite INSERT.

PostgreSQL

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);

Info

Learn more about PostgreSQL INSERT.

Firebird

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');

Info

Learn more about Firebird UPDATE OR INSERT.

Oracle

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');

Info

Learn more about Oracle hints.