In our SQL Server career, transferring or coping SQL table from one SQL server database to another is very regular and common. There is nothing new about it, we can do the same by generating table script with data or using import export wizard available in SSMS but why BCP? because BCP has couple of real advantages over SSIS and “generate script” methods for transferring table data from one server to another. If you have a very huge table and/or there are binary data in it then “Generate script” method will fail though it is a very convenient method for small tables having less amount of data. In case you want to transfer or copy SQL table from one server to another where a connection can not be made between them due to any reason then the SSIS method will also not work, BCP will be the ideal suggestion there.
Notes: BCP does carry the data but without table definition, you need to make sure that the table is already there at the other end where you want to move the data. You can have the bcp queries executed in SSMS or in windows command prompt or as a batch script. Please follow the below screen capture to enable SQLCMD mode in SSMS.
Step One: (Prepare the BCP file)
!!bcp “SELECT * FROM HumanResources.Employee” queryout “E:\others\Lab_SQLIndia\Employee.bcp” -N -S “MY-PC\SQLINDIA” -d “AdventureWorks2012″ -U”sa” -P “sa123”
The above command is pretty self descriptive, This will prepare a .bcp file to the file system with data in it as per the query.
Step Two: (Load the BCP file)
Once the .bcp file is ready, copy it over to the destination server where you want the table data to be moved.
!!bcp “Employee” IN “E:\others\Lab_SQLIndia\Employee.bcp” -N -S “MY-LAB\TESTSERVER” -d”TestDB” -U”sa” -P “sa123”