A brief introduction for bcp

I end up using the bcp utility to move tables between environments, and I’ve found that a lot of the guides on the internet go into great detail on how to use it. That’s fine if you have a lot of time. Here’s my brief introduction so that if I forget how to use it, I can reference this blog post.

Where to get bcp

It was already installed on this computer when I went to write this blog post, so I either installed it earlier or it comes with some of the SQL Server Management Studio tools. Either way, there’s a download at the link above.

What we need to know

Start up Command Prompt, probably as Administrator to be safe. Then run bcp to get some help information.

That’s a lot of parameters. Let’s talk about the ones we need.

-S "Server Name here"

-T OR -U and -P  
This is for authentication. -T for trusted, or Username and Password

-d "Database Name here"

-n Native type
Do this if you're importing back to SQL Server

Example time!

Let’s look at an example. Our test table is called Test.

bcp Test out "C:\Temp\TestTable.dat" -N -T -S "Server Name here\Named Instance here" -d "DBA"

We’re exporting the Test table to the C:\Temp folder, using Windows Authentication. We could replace the word “Test” with a query if we didn’t want to export the entire table.

Want to load data into SQL Server? Just change the “out” to “in.”

What about really big data? Well, the default batch size is 1000 rows. You can change this value when importing, but not when exporting. That’s a little confusing.

That’s all I have to say about bcp. I hope this is useful if you ever need to move simple data quickly. Stay tuned!