Data migration

Note: This feature is available in Community, Enterprise, Ultimate and Team editions only.

Overview

Data Migration in DBeaver provides the functionality to transfer data between different databases or between tables within the same database. This guide walks you through the steps to execute a data migration using DBeaver.

Data migration process

Step 1 Define the data source

To initiate the data migration, you need to select your data source. Follow the steps below:

1. Navigate to the Database Navigator.
2. Select one or multiple tables that you want to export.
3. Right-click to open the context menu.
4. Choose Export Data from the options.

Tip: You can export data from a custom SQL query. Execute the query and then choose Export Data from the results context menu.

Step 2 Define data transfer target type

After selecting the data source, the next step is to specify the type of destination for the data transfer. Choose Database type as the data transfer target and press Next.

Step 3 Tables mapping

Once you have chosen Database as the transfer target type, the next step involves mapping data. This process includes specifying options, selecting the target container, and setting other configurations. Configure your data mapping settings and press Next to proceed to the next step.

Available options

Buttons:

Icon	Option	Description
	Browse	Allows selection of target container.
	Configure	Opens additional settings. For details, see the Configure section.
	Preview data	Shows a preview of the data to be transferred.
	Up	Moves the selected mapping closer to the head of the queue.
	Down	Moves the selected mapping closer to the tail of the queue.
	Mapping Rules	Choose how new tables and column names are transformed when transferring data. For details, see Mapping Rules section.
	Auto assign	Automatically populates the Target column based on the source.
	Choose...	Specify the target container where the data will be transferred in.

Fields:

Option	Description
Target container	Defines the database or schema for the data transfer. For more details, see the Target Container section.
Source	Displays the names of selected tables and their columns.
Target	Shows the names of destination tables.
Mapping	Lists the available actions for data transfer. For more details, see the Mapping section.
Transform	Allows for the transformation of column values during the data transfer. For more details, see the Transform section.

Target container and target table

Specify target container

Press the Choose... button to select the container where the data will be transferred.

Define a target table

You can specify the target table where the data will be transferred in multiple ways:

• Manual Entry: Click on a cell in the Target column and manually enter the name of the table where you wish to transfer the data.

• Drop-down list: Use the drop-down list next to the Target column to choose among the following options:

  • Pre-existing table names.
  • Skip: Skips the data transfer for this table.
  • Browse: Opens the Choose Target Table window

• Browse button: Alternatively, press the Browse button to select a table from the existing tables in the target container.

  

Mapping

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

Action	Description
Create	Transfers source data into a newly created table or column in the target container.
Skip	Does not transfer the source data.
Existing	Transfers source data to an existing table in the target container.
Recreate	Recreate the table, which means that the available data, keys, indexes, and other possible entities of the existing table will be lost.

Tip: 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.

Transform column values

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:

Option	Description
Set to NULL	All values in the corresponding column are set to null.
Constant	Sets column value to a constant value.
Expression	Uses JEXL expressions to calculate the column's value.

Configure

Click the Configure button to open the Configure metadata structure window. This window allows you to delve into additional settings are distributed across the following tabs:

Column mapping

By navigating to the Column mapping, you can explore detailed mapping between the source and target columns.

Element	Description
Source Column	Contains names of columns existing in the selected source table.
Source Type	Lists the data types assigned to the columns in the selected source table.
Target Column	Contains names of columns in the target table where the data from the source column will be transferred.
Target Type	Lists the data types that will be assigned to the columns in the target table.
Mapping	Contains the list of actions to be applied to the data on data transfer.
Transform	Displays transformations for the data in a column during the transfer.

Important: Data types that are supported in the source database may not be supported in the target, and vice versa. To set a data type for a target column, click the cell in the Target Type column and choose from the dropdown list.

Table properties

By selecting the Table properties tab, you can modify properties of the target table, such as:

Property	Description
Tablespace	Specifies the tablespace for the target table.
Partition By	Sets the partitioning for the target table.
Comment	Allows you to add comments to the target table.

Note: The availability of these settings may vary depending on the database you are using.

Target DDL

By selecting the Target DDL tab, you can view the SQL script that will be executed during the data transfer.

If you're exporting data to a new table or recreating an existing one, the tab will display the necessary SQL statements. If not, the DDL tab will remain empty.

Mapping Rules

Clicking the Mapping Rules button opens a window that provides options for customizing how new tables and column names are transformed during the data transfer.

In the window, the following settings are available:

Option	Description
Name case	Sets the letter casing for table and column names. Choices include Default, Upper case, and Lower case.
Replace spaces	Determines how spaces in table and column names are handled. Options are: Do not replace, Replace with underscore, and Remove, convert to CamelCase.
Max data type length	Sets the maximum length for data types.
Save current changes to global settings	If checked, saves the current mapping rules to global settings. Otherwise, they will be saved at the data source settings level.
Global settings	Opens the global settings window for Names Mapping Rules. Alternatively, to open global settings, go to Window -> Preferences -> Connections -> Data Transfer

In addition to using the Global settings button, you can also access these settings by navigating to Window -> Preferences -> Connections -> Data Transfer.

Important: After modifying the Mapping Rules settings, you will be prompted to confirm your changes. Modifying the Mapping Rules may result in the loss of names that were already changed.

Keyboard Shortcuts

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

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

Step 4 Extraction settings

After setting up your table mappings, the next step is to define how the data will be extracted from the source. The Extraction settings tab offers various options to optimize this process. Configure these settings to suit your specific data transfer requirements and press Next.

Option	Description
Maximum threads	Defines the number of threads to be used for data transfer.
Extract type	Use Multiple queries if database doesn't support resultset scrolling. You can also set the Segment Size value when this option is selected.
Open new connection(s)	If checked, a new connection is established, ensuring data transfer does not affect other database operations.
Select row count	Enables a progress bar to display data migration status.
Fetch size	Indicates the number of rows fetched per server round trip, affecting extraction performance.

Step 5 Data load settings

After configuring the extraction settings, you'll need to specify how the data will be loaded into the target database. The Data load settings tab provides a range of options to control this part of the process. Adjust the settings according to your needs and then press Next.

Data load settings tab defines 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.
Send results by E-Mail	Sends data transfer results by E-Mail on finish. Read our guide to using email to transfer data to learn more.

Step 6 Confirm

The final step before initiating the data transfer is the Confirm tab. This tab provides a summary of all configurations set up in the previous steps. It serves as a last review to ensure all settings are correct.

Here is a table summarizing the configurations:

Option	Description
Source Container	Displays the source container from which data will be pulled.
Source	Shows the specific source within the container.
Target Container	Displays the target container to which data will be pushed.
Target	Shows the specific target within the container.
Source Settings	Summarizes settings related to data extraction from the source.
Target Settings	Summarizes settings related to data loading into the target.

These settings are final and cannot be changed at this stage. If all configurations are correct, click Finish to start the data transfer.

Tip: You can save these configurations for future use and create a task related to the data transfer operation by selecting Save task. This option consolidates your settings into a reusable task.

Upon clicking Proceed, the data transfer process will initiate.

Step 7 Export completion notification

If there are no errors, you will see a notification window indicating the successful completion of the export task. You can continue working with your database during the export process, as it will be performed in the background.