Migrate Data Between Two Databases with BCPPosted: December 12, 2013
------ 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.