Copy huge tables data from one database to another in SQL Server.

Question

I have two database say DB_A and DB_B. In DB_A database, table having huge data (few tables are having 2 to 10 million data). I want to move the all table data from DB_A to DB_B database. Please help me on writing stored procedures to move efficiently (fast) the data from one database to another.

Answer 1

The issue is how to handle your transaction logs. It has to write to both, but you should handle it in chunks.

So... Try something like this:

While exists (select * from db1.dbo.tablename)
Begin
 Delete top 100 from db1.dbo.tablename
 Output deleted.* into dbo.tablename;

 Checkpoint; 
End

Answer 2

No need to reinvent the wheel, have you considered using SQL Server Replication in order to move your database data?

Using Transactional Replication for example, you could define a Publication of the database tables that you wish to be copied/replicated.

You can then either syncrhonize the data on an ad-hoc basis, using a Snapshot, or if you wish to keep the data up to date in "near real time" then you could use continuous replication.