Data migration - bobbyiliev/dbeaver GitHub Wiki

DBeaver supports data migration of tables from one database to tables of another one.

To perform a data transfer, please, follow the steps below.

In the Database Navigator select one or more tables you want to export. In the context menu choose Export Data. (Note: you also can export data from the custom SQL query results. For that, choose Export data in the results context menu).

In the opened dialog box choose Database type as the data transfer target and press Next.

For proper table mapping, the following options are available:

• Target container - defines a database or a scheme where the data will be transferred to. Press button and choose the container.

• Source - contains names of all the tables selected at step 1. You can also see the list of columns existing in the source table by pressing

• Target - contains names of the tables where the data will be transferred to.

• Mapping - contains the list of actions to be applied to the source data on data transfer. The following options are available:

  Create- the source data will be populated into a newly created table or column of the target container.
  Skip - the source data will not be transferred to the target container.
  Existing - the source data will be transferred to the table that already exists in the target container.
  Unassigned - this value is set by default when there is no target defined.

If the cells are marked with , it means that in the target table there are no columns with matching names, otherwise the names will be filled in automatically.

You may also want to transform the values of some columns during the transfer. To do that, define column transformers by clicking on corresponding cells in the Transform column. You can choose one of three options:

• Set to NULL. All values in the corresponding column will be set to null.
• Constant. All values in the corresponding column will be set to the specified constant.
• Expression. This transformer uses expressions (namely, JEXL expressions) to calculate the cell's value. You can use basic arithmetic operations and column names to construct an expression.

You can define a target table by clicking on a cell in the Target column and entering its name, or press the New button and enter a new name in the opened dialog box.

You can also choose a name for a target table from the drop-down list.

Or select a table from the existing tables in the target container by pressing the Browse button

To define the mapping setting for a column in a target table, release the list of source table columns by pressing next to the table's name in the Source column, then click the name of the target column and enter a new one or select one from the dropdown list. To collapse the list, press

If you want tables of the target container to be named like those of source, press the Auto assign button and the Target column will be automatically populated.

You can also define the names of target columns, as well as their data types, by clicking a row with a table name and pressing the Columns button

The following elements are available here:

• Source column -this column contains names of columns existing in the selected source table;

• Source type - this column contains the list of data types assigned to the columns in the selected source table;

• Target column - this column contains names of target table columns where the data from the source column will be transferred to. To change the name, click the cell and enter a new name.

• Target type - this column contains the list of data types that will be assigned to the columns in the target table.

  IMPORTANT: Sometimes data types that are supported on the source database are not supported on the target or vice versa.
  To set a data type for a column in a target table, click the cell in the Target Type column and select one from the dropdown list of data types supported on the target.

• Mapping - this column contains the list of actions to be applied to the data on data transfer.

To change the mapping type, click a cell in the Mapping column of Table mapping dialog box and select the required mapping type.

You can also view the SQL script that will be run on data transfer by pressing the Target DDL button .

If you want to see a preview of the imported data, you can select the Preview data button

The following keyboard shortcuts for easy navigation within the mapping table area of Table mapping screen are supported:

Shortcut	Action
Up	Moves one row up.
Down	Moves one row down.
Right	Releases the list of source table columns.
Left	Swaps the list of source table columns.
Space	Auto-assigns the target.
Del	Sets mapping type to skip.

Configure data mapping and press Next.

Data export settings are grouped into Extraction settings and Data load settings.

Extraction settings define how the data will be pulled from the source. The following options are available:

Option	Description
Maximum threads	Defines the number of threads to be used for data transfer.
Extract type	Select Single query option if your data load is not too big. Select By segments option if you need to migrate a solid amount of data. When this option is selected you can set the Segment size value, that is to define the number of rows to be transferred in each segment.
Open new connection(s)	If selected, a new connection will be opened and the data transfer will not interfere with other calls to the database whose data is being transferred.
Select row count	If selected, a progress bar displaying data migration process will be shown.
Fetch size	Specifies the number of rows to fetch per server round trip. This setting has a significant impact on the extraction performance during data retrieval.

Data load settings define how the extracted data will be pushed to the target. The following options are available.

Option	Description
Transfer auto-generated columns	Fill in or skip columns marked with the "autogenerated" status. Some databases accept values in such columns, while others will throw a syntax error.
Truncate target table(s) before load	Select this checkbox only if you want all the data to be cleared from the target table. Be very careful with this option!
Disable referential integrity checks during the transfer	Disabling constraints in the target table. This setting prevents database errors by temporarily disabling the constraints. However, please note that not all databases support this functionality.
Replace method	Read our guide on Data Import and Replace to learn more about the replacing method option.
Open new connection(s)	Use this option to speed up data transfer. If selected, a new connection will be opened and the data transfer will not interfere with other calls to the database where data is being transferred to.
Use transactions	This option allows you to speed up the data transfer and to define the number of rows for each transaction by setting the Commit after insert of parameter.
Do Commit after row insert	Performing a commit after a certain number of inserted rows. This setting specifies that a commit operation should be executed after a specified number of rows have been inserted into the table.
Use multi-row value insert	Use multi-row insert with extended values number for higher performance. Database-specific setting.
Skip bind values during insert	This option can drastically increase performance for some drivers like Redshift by skipping a process of binding values and setting them directly, but it opens up a vulnerability to SQL injections. Not recommended if you are not sure of imported file contents.
Disable batches	Select this checkbox if you want to disable the use of batch imports. The import will be made row by row. Enabling this function will show all import errors, but make the import process slower.
Ignore duplicate rows errors	In the import process, if a database encounters a duplicate key from the import row in the target table, such errors are ignored, and the import operation continues without failure.
Use bulk load	Bypasses transaction settings and loads the entire dataset using the native tool provided by the database.
Open table editor on finish	If selected, the table editor is to be opened when data transfer is finished.
Show finish message	If selected, a notification message will be shown when the transfer is finished.
Save task	Opens the Save Task window to assist in creating a task during the data transfer process. This window provides options and settings for creating and configuring a task related to the data transfer operation.

Check out the data transfer settings and press Start or save as task.