Migrate Data Between Two Databases with BCP


 
 
------ bcp out
exec master..xp_cmdshell  'bcp [myDB1].[dbo].[theTable] out "C:\temp\theTabledata.dat" -T -c -b 100000';

----bcp in
exec master..xp_cmdshell  'bcp [myDB2].[dbo].[theTable] IN "C:\temp\theTabledata.dat" -T -c -b 100000';


 

It took 40 minutes to export the table to data file and it took 36 minutes to import these (over 323 million) rows.
The theTabledata.dat file size is about 37G. All indexes from target table are dropped before importing.
The -b switch is important to set a batch size.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s