Make data transfer from one database to another

22.01.19 IT / Database 6407

Modern software systems are diverse - different data structures and formats are used. But be that as it may, the data in such systems is of great value. Indeed, at any time the system can be replaced with another and it may be necessary to transfer of data from one database to another . A brief overview of this topic will be given below.

Obviously, two different systems have different databases (DB) - their type, table structure, and so on may differ (databases with SQL support are usually used). How, then, to extract the data and transfer it to another database while preserving all the information? It is difficult to do without the help of a specialist, we will briefly consider what he will need to do.

First of all, you need to access the old database and get an idea of ​​its structure. After drawing up the schema of the old database, you need to analyze the schema of the new database - where the data will be imported. Finally, all that remains is to use a convenient programming language (PHP, C ++, etc.) and write code to extract data from one database to another. When the code is ready, you need to carefully test it and you can begin to start the data transfer process between databases.

The structure of the code for transferring data between databases is standard. Typically, a scheme is used in which the code first fetches all the data from the database and puts it into an associative array. The keys of such an array are the names of the columns, and the values ​​of the array, respectively, are the values ​​of the columns.

Since the names of the columns most likely differ between the bases, it will be necessary from the above analysis to compose an array in the code for transforming the data. Namely, such an array will indicate the correspondence between the name of the old column and the name of the column in the new database. The data will remain the same, only the column names will change.

When the array with data from the old database and the array for transformation is ready, a loop is used to traverse the array and insert data from this array into the columns in the new database in accordance with the correspondence of the columns of these two databases.

You need to take into account the fact that the amount of data can be significant and therefore it will take a lot of time for the code to work. Therefore, it is important before starting to properly configure the server for export and import of data between databases data:

• remove restrictions on the running time of the script;
• allocate enough RAM;
• use a powerful processor, etc.

But that's not all, since the speed of data migration will depend on the brevity and speed of code execution. Therefore, the code must perform a minimum of queries to the database, it must be strictly optimized. If the capabilities of the database allow, you should try to make one massive query instead of single queries, which can be divided into transactions if necessary.

Thus, transferring data from one database to another is a feasible task that should be entrusted to a specialist. If you have long wanted change your system to another (its type is not important - web, desktop software, etc.), but you hesitate because of data transfer - don't worry, you can install a new system and perform a lossless data transfer.