Sql-server – How to copy data from one database to another database efficiently

csql server

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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server – Copy data from one database to another – identical tables, new identity values needed

Unless I'm missing something your problem is not moving the data it's dealing with the identity values that are already set up. If that is the case then try this.

Pick a value greater than your current ident values on either DB. I would pick a round value, say 1,000,000.
Pick the ident values you want to change (for example if you have lookup tables that are the same for both DBs then you probably want to leave them the same.)
When you move your data add the value you selected in step 1 to the idents value you want to change as you move them.

As long as you are consistent in the value you are adding all of your relationships will stay the same into the combined DB. So for example

You have an employee table with a current max id of 200,000 in DB A and 1,400,000 in DB B. You decide to move the data from DB A to DB B because it means moving less data. As you move your employee table you add 2,000,000 to the Employee_Id column.

USE DatabaseB
GO

SET IDENTITY_INSERT Employee ON

INSERT INTO Employee (Employee_Id, Other_Columns)
SELECT Employee_Id + 2000000, Other_Columns
FROM DatabaseA.dbo.Employee

SET IDENTITY_INSERT Employee OFF
GO

And last piece of advice back up everything before you start in case you make a mistake :)

Sql-server – SMO Copy database or remove all non-system database objects

You could use SELECT * FROM sys.databases d WHERE d.name = '<target_db_name>'; to obtain the existing settings for the database prior to running the transfer. Store the results in a DataSet so you can potentially use them later.

If the database does not exist, you'll get no rows returned. If the database does exist, you could drop the existing database as the first step.

After you run the TransferData() method, you could apply the necessary configuration changes based on the items in the DataSet. For instance the recovery_model_desc column contents could be used to set the recovery model by running this code: ALTER DATABASE <target_db_name> SET RECOVERY = 'xxx'; where xxx is the value contained in the column.

Related Question