How to copy data from one database to another database efficiently?

Question

I have a conceptual question.

I have two databases which theoretically have the same structure but one of them is a SQLBase database and the other one is a SQL Server database.

One database (the SQLBase database) already contains a lot of data. This data should be transferred to the other (SQL Server) database via Select and Insert.

How can I do this data migration with the highest performance?

My first approach was to sort all the tables in a list where the tables which contain foreign keys will be stored behind the referenced tables. But with this solution it will be impossible to start parallel processing.

The second idea was to create a custom type which contains the tablename and the tablenames of the referenced tables and a bool flag which stores whether the data in the table have been copied. This type is stored for each table in a list. Then I start a new thread that checks before copying whether the referenced tables have already been created for each table. If not, I execute Thread.Sleep() after which I will check it again.

Is there a well performing approach to this problem?

Answer 1

What about using one of the existing ETL tools? Talend ETL or Pentaho Data Integration are two examples (performance-wise PDI is better). You can design an ETL job then schedule to run it as you like.

Answer 2

I would suggest to use SSIS for this task.