Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 305 Importing and Exporting Data with BCP The first thing to understand about BCP, the Bulk Copy Program, is that it s not a SQL Server command. It s not part of T-SQL. If you attempt to use BCP in Query Analyzer, it does everything it can to just laugh at you. Don t do it; it doesn t work. BCP is a command- line tool. That s right: the big, black empty window with the blinking cursor command line. So fire up a command prompt and dig in. BCP is ancient, in computer years anyway. It s part of the wild history of SQL Server, and has been part of SQL Server since at least version 4.21, back when it was still a joint development effort between Microsoft and Sybase. The reason it s still around is that it s an extremely useful tool for loading data into a database quickly. The reason it s a command-line tool is all about overhead. Keep in mind that you can run BCP across the network; it doesn t have to run on a server. You can have a bunch of servers all across your network using BCP at once, and, assuming you have enough disk speed, SQL Server just sits there and soaks up data. BCP has lots of command-line options. The basic syntax is: bcp
.
The
is the destination table, usually specified as a three-part name, like Chapter5.dbo.sales. The is what direction. Telling BCP to go IN tells BCP to read from the file and put data IN SQL Server. Telling BCP to go OUT pulls data OUT of SQL Server and writes it to a file. The is the name of the file that you want to use. If it s an IN operation, then the file should exist and have data in it. If it s an OUT operation and the file exists, the file gets overwritten by the data coming out; otherwise, the file is created. The is the name of the server that you re trying to use, and either username and password or a note to use your Windows authentication to handle it. Finally,
tells BCP what kind of format the data is in. BCP can deal with three data formats: native, character- delimited, and column-delimited. Native format works only when you re moving data from one SQL Server to another, and the servers have to use the same collation and character set for it to work. It s NOTE Bee Sea Pea If you re looking for help with BCP, or just trying to find articles on Microsoft s support web site, you can just look for the acronym BCP. It s one of those so common people usually don t remember what the letters mean acronyms.
If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.
This entry was posted
on Monday, July 27th, 2009 at 5:23 am and is filed under sql.
You can follow any responses to this entry through the RSS 2.0 feed.
Responses are currently closed, but you can trackback from your own site.