Data compare

Note

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

DBeaver lets you compare tables and query results to spot differences, track changes, and generate SQL scripts to sync your data.

You can compare:

• Two tables - select a pair and compare them directly.
• Multiple tables - select several tables at once and assign each a target to compare against.
• Query results - use a SELECT query as a source or target instead of a table.

Info

You can compare objects from the same or different databases, or even between different Relational Database Management Systems (RDBMS), such as PostgreSQL and MySQL.

How it works

1. Open the wizard - select your tables or queries
2. Manage compared objects - add pairs, assign source and target
3. Map columns - match columns between source and target
4. Configure keys - set which columns identify each row
5. Adjust settings - set limits, output format, and export options
6. View results - explore differences and export the SQL diff

Open the Data Compare wizard

1. Select the objects you want to compare:

   • from the Database Navigator:

     Tip

     You can select more than two objects.

     1. Open the context menu, navigate to Compare/Migrate

        

     2. Choose an option:

        • Data Compare - opens the wizard with the selected objects added as source inputs. You then assign a target table or query for each source.
        • Data Compare between Selection - opens the wizard with the two selected objects prefilled as a source-target pair.

        Note

        Data Compare between Selection option is available only when exactly two objects are selected.

   • From the SQL Editor:

     1. Open the SQL Editor and write a SELECT query

        

     2. Select the query, open the context menu, and choose Execute -> Data Compare from query.

   Tip

   You can also start the compare through AI by sending a short request in AI chat. See Data transfer actions for supported commands.

2. The Data Compare wizard opens.

Manage compared objects

Use the Compared objects page to set up what you compare. Here you add and remove pairs, assign source and target, and configure column mapping and key columns for the selected pair.

Tip

The wizard is prefilled based on how you start it. Data Compare between Selection prefills the source and target. Data Compare prefills only source. You then choose a target for each source.

Add a source and assign a target

1. On the Compared objects page, click one of the toolbar buttons:

   • click Add table(s) as a source button () to add one or more objects as source
   • click Add query as a source button () to add an SQL query result as source

2. For each added source, assign a target:

   • Click Browse... in the target column, then pick a table, or choose a query (depending on what you want to compare)

Tip

If you add multiple source objects, the wizard creates multiple pairs. Assign a target for each source row.

Remove a pair

Select a row in the list, then click Remove selected pair button ().

Change source or target

Use the context menu on a cell in the source or target column:

• Replace with a table: replace the current side with another table
• Replace with a query: replace the current side with an SQL query result
• Swap source and target: swap the pair

Tip

You can also replace a table or query by double-clicking the source or target cell.

Column mapping

Use column mapping to control which columns are compared, and how source columns match target columns. This is useful when objects have different column names, order, or structure.

1. In the top list, select the source-target pair you want to configure. The column list below updates for that pair.

2. In the bottom list, review the mappings:

   • use the Compare checkbox to include or exclude a column from comparison
   • open the Target cell dropdown to map the source column to a different target column
   • if a column has no match on the other side, it’s shown as <unspecified>

3. (Optional) click Auto assign columns button () to match columns automatically.

Note

Key columns are handled separately. They are used to match rows, not as regular mapped columns.

Keys configuration

Keys are used to match rows between source and target. Rows are sorted and compared by the selected key columns, so keys must uniquely identify each row.

1. In the top list, select the source-target pair you want to configure.
2. Click Choose constraints button (). The Configure constraints dialog opens.
3. In the dialog, select one or more key columns for source and target, then click OK.

Note

The number of selected key columns must be the same on both sides.

Important

Make sure the selected key columns are unique, or the comparison results may be incorrect.

Composite keys

When you select multiple columns, they form a composite key. Composite keys uniquely identify rows based on a combination of values across the selected columns.

Tip

Select columns for composite keys that, together, uniquely identify each row. Avoid columns with duplicate or null values, as they may impact comparison accuracy.

Compare settings

On the final page of the Data Compare wizard, configure settings to tailor the data comparison to your needs:

Setting	Description
Open viewer on finish	Opens the compare viewer for a visual overview of the differences once the comparison is complete.
Export compare results to file	Exports the SQL script diff to a file. You can choose the output format and specify the output file path.
Apply changes to	Chooses which table the exported script updates. Pick Source table or Target table to define where the diff is applied.
Output order	Controls how changes are sorted in the exported result. Default keeps the comparison order. By type groups inserts, updates, then deletes.
Output format	Chooses how the results are written to the file. You can export an SQL script or an HTML table.
Output file	Sets the path and file name where DBeaver will save the exported diff.
Limit compared rows to	Limits how many rows must be compared against each other before ending the comparison.
Limit different rows to	Limits how many rows must be differ between each other before ending the comparison.
Limit fetch size to	Determines how many rows should be fetched at once, which helps manage memory and performance during comparison.
Show modified rows	Determines whether modified rows should be included in the diff or not, allowing you to track changes in the data.
Show deleted rows	Determines whether deleted rows should be included in the diff or not, providing insight into data that has been removed from the database.
Show inserted rows	Determines whether newly inserted rows should be included in the diff or not, allowing you to see additions to the database.
Store results in memory	Stores results in memory for further processing. Note that DBeaver might run out of memory when comparing large tables if this option is enabled. When disabled, results will be stored in a temporary database.
Open new connections	Opens new physical connections for data reading, which can help manage resources and ensure the stability of data comparison operations.
Query rows count	Queries row count before performing compare. This lets you track the progress of the comparison but may cause performance faults in some cases.
Save task	Opens the Save Task window to assist in creating a task during the compare process. This window provides options and settings for creating and configuring a task related to the compare operation.

Tip

You can export file to a remote file system via Cloud Storage using the Browser remote file system button .

Execution log

Once you have configured all settings and clicked Proceed, the Data Compare wizard will perform the comparison operation:

• an Execution Log window will display the progress and outcome of the comparison process.
• simultaneously, a separate window will open showing the comparison results. This window provides a detailed view of differences between the datasets based on the specified settings

Viewing the results

After the comparison process is complete, you can view the results in the viewer. It highlights the differences between the datasets in detail, with each row that shows a discrepancy being clearly marked.

Note

A complete visual preview of the results is available only when Data Compare is launched through the wizard. If launched as part of a task, the visual editor will not open. Instead, only statistics will be recorded in the execution log.

The viewer not only displays the differences but also provides tools to manage and interpret these differences effectively.

Feature	Button image	Description
Export changes		The results of the Data Compare can be exported as an SQL script, which includes DELETE, INSERT, and UPDATE statements to make the data in the second table match the first. Alternatively, you can export the results in an HTML format as a table for easy viewing and sharing.
Swap tables		The Swap tables button allows you to switch the positions of the source and target data panels within the viewer.
Show summary		By clicking the Show Summary button, you can access a statistical summary and analysis of the differences found between the datasets. This summary provides a quick overview of the total changes, including the number of modified, deleted, and inserted rows.

Tip

You can also export the comparison results table through Export data button in the bottom toolbar or copy rows or individual values that differ.