How to copy data from one database to another
Some daily operations require database administrators to duplicate databases or transfer data from one database object to another for testing purposes. For example, if you need to copy some specific data from one table to another within a particular time period, you can use dbForge Data Compare for MySQL. The tool allows you to filter data for comparison using the WHERE Filter editor, in which you can specify a range of dates for source and target tables.
Let’s copy data from the sales.orders table filtered by the orders from April 1 to April 30 of the BicyleStore database using the New Data Comparison Wizard. Then, move it to the BikeStores database using the Data Synchronization Wizard.
To transfer data between two tables, do the following:
1. On the toolbar, click New Data Comparison to initiate the data comparison.
2. In the New Data Comparison wizard that opens, choose the server connections and databases you want to compare and click Next.
3. Optional: On the Options page, customize the default comparison options.
4. Go to the Mapping page, select the database object whose data you want to filter and click Filter on the toolbar.
5. In the WHERE Filter window that opens, type a valid Transact-SQL WHERE clause. Alternatively, you can right-click the space, select the required column, and enter the expression.
To verify the WHERE clause, click Validate. To apply the expression to both the source and target database objects, select the Use the same expression for Source and Target checkbox.
Note
To learn more about how to configure the WHERE filter in dbForge Data Compare for MySQL, refer to Configure data filter options.
6. Click OK to save the changes. The object to which the filter will be applied is now displayed with a filter icon in the grid.
7. At the bottom of the wizard, click Compare to run the comparison process. The comparison document opens displaying the results in the upper grid. The lower grid shows the differences in the rows that are all selected for synchronization by default. To exclude the rows from the synchronization, clear the checkboxes next to the corresponding rows.
8. After you selected the rows you want to move to another table, click Synchronize data to the target database to initiate the synchronization.
9. In the Data Synchronization Wizard that opens, select Execute the script directly against the target database and click Synchronize to synchronize the data.
In the wizard, you can also customize the default synchronization options or add custom scripts to be executed before or after data synchronization.
Want to Find out More?
Overview
Request a demo
