Skip to content

Data import and replace

When you import data into a table, use the On duplicate key setting to choose how CloudBeaver handles rows that contain a primary key or unique key value already present in the table.

Select a duplicate key method

Follow the steps in Data import. On the settings step, select a method from the On duplicate key list, then select Import.

The list contains only methods supported by the target database. Select None to use the default insert behavior.

Note

The On duplicate key list is unavailable when you select the Use bulk load checkbox. Bulk loading uses a separate database loading path.

Understand duplicate key methods

The method changes the SQL operation used when an imported row has a duplicate primary key or unique key value:

  • The default insert method inserts rows without special duplicate-key handling. Duplicate keys can cause an import error.
  • An ignore method keeps the existing row and skips the imported row.
  • A replace method replaces the existing row with the imported row.
  • An update method keeps the existing row and updates it with values from the imported row.

Note

The available methods depend on the target database.

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.