Professional Microsoft® SQL Server® 2008 Programming

Chapter 15. Buying in Bulk: The Bulk Copy Program (BCP) and Other Basic Bulk Operations

If your system is going to be operating in something of a bubble, then you can probably skip this chapter and move on. Unfortunately, the real world doesn't work that way, so you probably ought to hang around for a while.

For most systems, there will eventually come a time (often, it's many times) when you need to move around large blocks of data. Sometimes you need to bring in data that's in the wrong format or that's sitting in another application's data files. Sometimes, you need to extract data directly from another system. The good thing is SQL Server has two tools to help you move data fast — the Bulk Copy Program (bcp) and SQL Server Integration Services (SSIS). In this chapter, we'll be looking primarily at the first of these. In addition, we'll take a look at bcp's close cousins — the BULK INSERT command and OPENROWSET (BULK).

We will examine SSIS in the next chapter.

bcp is something of an old friend. You know the one — where you hardly ever see them anymore, but, when you do, you reminisce on all the crazy things you used to do together. It was, for a very long time, the way we moved around large blocks of data; and it did so (still does as far as that goes) amazingly fast. What, however, it lacks is sex appeal — well, frankly, since SQL Server 7.0, it has lacked appeal in a whole lot of areas.

So, why then am I even spending a chapter on it? Well, because bcp still definitely has its ...