Microsoft SQL Server BCP Example

Have you ever needed to copy data from one Microsoft Sql Server to another Sql Server not on the same server. I found out that using BCP (Bulk Copy Program) is a very useful. BCP is a command-line utility that ships with Microsoft SQL Server.

The first thing is to create a .bat (open notepad and save as source.bat) file that runs your query and puts the result into a file. Here is how I did it:

bcp "SELECT * FROM database.dbo.table" queryout "C:\Inetpub\wwwroot\www.domain.com\db-bcp-files\tablename.bcp" -T -c
    pause

The first line has the query and location of where to put the file. You will have to look at all the options you have. I put a pause in only because I want to see the results and can hit any key to end. Also note that you can have multiple lines that pull from all your tables instead of running a batch for each table.

Once you have your files you need to transfer the files to the target system and create another batch that imports the data like so:.

bcp database.dbo.table in table.bcp -Slocalhost\SQLEXPRESS -T -c
    pause

**NOTE: My target is for a SQL Express database so you may have to use an ip or domain you will have to use.

That's it, check your tables and you should see your tables now with data.