RDMS SQL data migration and replication from a Source table to multiple Destination tables.
Common software migration and replication tasks for database converters are to transfer data from one source to one destination with the field-mapping feature. This can be achieved, for example, using different ETL tools. How it can be done? Let’s say, firstly database is moved to dump file, after that this dump file is transferred to destination db by a console application. But this way is tedious!
Easier is to map Source fields to Destination fields using GUI tools.
Especially these tools are good when more complicated, non-standard scenario is required.
For example, sometimes there may be such a task as to migrate the Source table data to numerous Target tables. Often, users need to insert data from1 Source table into two or more destination tables depending on certain conditions as customers’ details, location, online store goods and so on.
The task can be even more difficult if Source and Destination databases are heterogeneous.
This post shows how to realize this scenario using DBConvert products.
As an example, let’s take one table in PostgreSQL as a Source. And you want to move it to three MS SQL destination tables.
Task
Migrate data from 1 Source table into three Destination tables
In what cases can such task appear? For example, your company decided to transfer it’s database to a completely new server. The current Source stores the data in a non-optimized format, and in addition to the data transfer task you can easily optimize the new database structure.
How can data normalization be useful? Data normalization is necessary
· to eliminate mess, data will not be duplicated, which leads to a significant reduction in the number of errors.
· to arrange data into logical groups so that each group describes a small part of the whole
· to built a database in which it is possible to quickly and efficiently access and manipulate data without compromising data integrity
But not everything is so warm and fuzzy. Usually the data processing in such databases takes more time, so the replication will be much slower.
Each company can find its pros and cons here, and it’s up to you to decide whether normalization is suitable for your needs or not. But let’s back to our scenario.
Solution
Source PostgreSQL database has 1 table “Customers” with the following fields:
And you need to copy the data from this Source table and insert into three Destination tables.
Suppose that way, as shown below, you want your Destination db to look like after migration process.
Except the table “Customers” itself, the Destination will contain two additional tables “Customers details” and “Address”. The picture above shows the transfer of fields to these tables.
Schematically these fields transferring can be represented as follow:
DBConvert program, during the process of migration and replication, relies on the structure of Source tables. As this Source has only one table, it needs to add two additional tables in the Source structure. The tables’ structure must contain such fields which you want to have in Destination db.
As table “Customers” already exists on Source side, we’ll create tables “Address” and “Customers Details”.
After these manipulations:
- open the GUI of our software
- connect to your Source and Destination databases
- on ‘Customization’ stage apply a filter to each of these tables in the Source db, selecting the data that you want to see in the corresponding table on the Destination.
After the conversion is complete, all three tables with needed data will appear automatically on Destination side.
Sometimes data replication needs to be done already to an existing Destination db/tables. If it’s your case, the scenario described above can also be successfully applied. Pay attention to the creation of missing tables in Source database —
the structures of Source tables must be IDENTICAL to the structures of Destination tables.
If the names of the fields in both databases are different, they easily can be reassigned/renamed:
- Click on the field in the tree-view. All information related to this field will be displayed on the right.
- In “Source field info” group, there is a name that this field has in Source database.
- In the “New field info” rename this field as it will be called in the target database. New name can be typed or selected from the drop-down list.
Conclusion
As you see there is nothing complicated and all manipulations are performed quickly and with minimal resources from your side.
Thanks for your attention! Awaiting for your questions and comments. If you have any custom scenario or task, please share in comments. We will realize it in DBConvert and discuss with you!
