Data Management

How do I use BCP in SQL Server?


The Bulk Copy Program (BCP) is a command-line utility that ships with Microsoft SQL Server. With BCP, you can import and export large amounts of data in and out of SQL Server databases quickly and easily. Any DBA who has utilized this functionality will agree that BCP is an essential tool.

BCP 101

You access the BCP utility from the command prompt. Here's the simple syntax:

bcp {dbtable | query} {in | out | queryout | format} datafile

[-n native type] [-c character type] [-S server name] [-U username]

[-P password] [-T trusted connection]

The command example I'm going to use starts with bcp followed by a fully qualified table name (database name, table or object owner, table or object name). For example, if you want to export the authors table, as part of the dbo group from the pubs database, you supply the full table name pubs.dbo.authors. Next, you use an in or out argument to specify whether you want BCP to copy data into or out of a database. You then specify the location of the datafile on your database server.

At this point, our BCP statement looks like this:

bcp pubs.dbo.authors out c: empauthors.bcp

When using BCP, don't forget that the switches are case sensitive. If you don't apply the right case, the BCP statement will fail.

Next, we'll add some of the basic command-line options. The –n switch specifies native SQL Server format. The –c switch is used when formatting the file using Char as a datatype. If you use this switch, you can easily open your file with Excel. The –S switch enables you to add the server/instance name. The –U switch allows you to add the name of the login used to connect to SQL Server. The –P switch lets you add the password of the –U switch, and the –T switch is for establishing a trusted connection to your SQL Server. Figure A shows our completed BCP statement.

If we were importing data to the authors table, our BCP command would look like this:

bcp pubs.dbo.authors in c: empauthors.bcp -c -Sstevenw -Usa –P That gives you an idea of the basics of the BCP utility, but there are plenty more switches at your disposal. In all, BCP supports 27 switches, which are shown in Listing A.

With this many switches available, I recommend that you play around with them to see which ones you might want to use. For example, the –e switch is handy because it will create an error file you can look at if your BCP command returns errors. Here is an example:

bcp pubs.dbo.authors out c: empsteventest.txt -n -Sstevenw -Usa -P -eC: emperror.txt

For more information on how to use the various switches, see SQL Server Books Online.

BCP and database objects

Let's create a new database called pubs2 and use BCP to move all the data from the pubs database into it. First, we need to create the database. Then, we'll generate the SQL script, run it on pubs2, and export the data from pubs into pubs2.

Start by creating the database in SQL Server Enterprise Manager and name it pubs2. Next, select pubs, right-click on it, and choose Generate SQL Scripts from the All Tasks menu. Now you are ready to set your scripting options. Click Show All and then select the Script All Objects check box, as shown in Figure B . Go to the Options tab and select all the check boxes under Table Scripting Options, as we've done in Figure C . You can preview your script from the General tab or click OK to create and save the script.

By performing the above, we are making a complete replica of the Pubs database. We could also have scripted out only one table or multiple tables. You can make the choice, depending on the task at hand.

Now, open Query Analyzer and run the newly created script (Figure D ). After the script runs, the database objects and indexes will have been successfully created (Figure E ).

To export the authors table out of pubs and import it into pubs2, we'll use the BCP statements we learned earlier:

bcp pubs.dbo.authors out c: emppubauthors.bcp –n –Sstevenw –Usa –P bcp pubs2.dbo.authors in c: emppubauthors.bcp –n –Sstevenw –Usa –P

Then, open the SQL Query Analyzer and run the following statements on the pubs2 and pubs databases:

Use pubs2

Select * from authors

Select count(*) from authors

Use pubs

Select * from authors

Select count(*) from authors

This allows you to see the full process of moving data come full circle.

Most DBA's today use DTS, Database Restore, and/or attaching and detaching databases to copy data from one server to another. All these tools will get the job done. But if you want to copy large amounts of SQL Server data quickly, BCP is still one of the best tools available. Once you get past the initial learning curve, you'll find that BCP offers a highly efficient way to copy data from one database to another.

6 comments
Henry_007
Henry_007

bcp command spaces some records when we use simple bcp tags. to over come this problem use bcp AdventureWorks2008..Contacts4 in C:\Data\PersonData_c.dat -f C:\Data\PersonFormat_c4.xml -S localhost\sqlsrv2008 -T -S and -T tags

dcs9
dcs9

Sorry for such a long question and table. I have a file which has both commas and " being used as delimiters. So I created a format file to input the data into my table. I created a table dbo.Testing for this and tried using bcp format file to input the data into it but after several attempts, and applying many permutations combinations it doesnt seem to work. Every time it gives the error SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Incorrect host-column number found in BCP format-file CREATE TABLE [dbo].[Testing]( [Company] [varchar](2) NULL, [Item] [varchar](15) NULL, [Item Description] [varchar](80) NULL, [IssueUOM] [varchar](2) NULL, [PackSize] [numeric](11, 4) NULL, [ShippingFormat] [varchar](10) NULL, [CartonHeight] [numeric](9, 4) NULL, [CartonWidth] [numeric](9, 4) NULL, [CartonDepth] [numeric](9, 4) NULL, [CartonVolume] [numeric](9, 4) NULL, [CartonWeight] [numeric](9, 4) NULL, [ShipperHeight] [numeric](9, 4) NULL, [ShipperWidth] [numeric](9, 4) NULL, [ShipperDepth] [numeric](9, 4) NULL, [ShipperVolume] [numeric](9, 4) NULL, [ShipperWeight] [numeric](9, 4) NULL, [CartonsPerShipper] [numeric](9, 0) NULL, [ShipperQuantity] [numeric](9, 0) NULL, [PalletTi] [numeric](9, 0) NULL, [PalletHi] [numeric](9, 0) NULL, [PalletWeight] [numeric](9, 0) NULL, [PalletQuantity] [numeric](9, 0) NULL, [PrimaryStockroom] [varchar](2) NULL, [Supplier] [varchar](8) NULL, [CountryCode] [varchar](3) NULL, [GTFamily] [varchar](15) NULL, [MinOrderQty] [numeric](11, 3) NULL, [FOBPurchasePrice] [numeric](15, 5) NULL, [FobCurrency] [varchar](3) NULL, [StandardCost] [numeric](15, 5) NULL, [StandardCostCur] [varchar](3) NULL, [JSP] [numeric](15, 5) NULL, [JSPCurrency] [varchar](3) NULL, [TariffCode] [varchar](8) NULL, [TariffCodeDesc] [varchar](30) NULL, [UPNBarcode] [varchar](17) NULL, [EANBarcode] [varchar](28) NULL ) In the format file I have used the following field delimiters as per the field having or not having double quotes, Field Starts With Field Ends With Next Field Starts Field Terminator Example 1. ??? ??? ??? ???\???,\?????? ???AB???,???CD??? 2. ??? ??? After Comma(Without ???) ???\???,??? ???AB???,123 3. After Comma (Without ???) Comma With ??? ???\,?????? 123,???AB??? 4. After Comma Comma After Comma ???,??? 123,123 The format file is as follows:- 10.0 37 1 SQLCHAR 0 2 "\",\"" 1 Company Latin1_General_CI_AS 2 SQLCHAR 0 15 "\",\"" 2 Item Latin1_General_CI_AS 3 SQLCHAR 0 80 "\",\"" 3 ItemDescription Latin1_General_CI_AS 4 SQLCHAR 0 2 "\"," 4 IssueUOM Latin1_General_CI_AS 5 SQLCHAR 0 41 "," 5 PackSize "" 6 SQLCHAR 0 10 "\"," 6 ShippingFormat Latin1_General_CI_AS 7 SQLCHAR 0 41 "," 7 CartonHeight "" 8 SQLCHAR 0 41 "," 8 CartonWidth "" 9 SQLCHAR 0 41 "," 9 CartonDepth "" 10 SQLCHAR 0 41 "," 10 CartonVolume "" 11 SQLCHAR 0 41 "," 11 CartonWeight "" 12 SQLCHAR 0 41 "," 12 ShipperHeight "" 13 SQLCHAR 0 41 "," 13 ShipperWidth "" 14 SQLCHAR 0 41 "," 14 ShipperDepth "" 15 SQLCHAR 0 41 "," 15 ShipperVolume "" 16 SQLCHAR 0 41 "," 16 ShipperWeight "" 17 SQLCHAR 0 41 "," 17 CartonsPerShipper "" 18 SQLCHAR 0 41 "," 18 ShipperQuantity "" 19 SQLCHAR 0 41 "," 19 PalletTi "" 20 SQLCHAR 0 41 "," 20 PalletHi "" 21 SQLCHAR 0 41 "," 21 PalletWeight "" 22 SQLCHAR 0 41 "\,"" 22 PalletQuantity "" 23 SQLCHAR 0 2 "\",\"" 23 PrimaryStockroom Latin1_General_CI_AS 24 SQLCHAR 0 8 "\",\"" 24 Supplier Latin1_General_CI_AS 25 SQLCHAR 0 3 "\",\"" 25 CountryCode Latin1_General_CI_AS 26 SQLCHAR 0 15 "\"," 26 GTFamily Latin1_General_CI_AS 27 SQLCHAR 0 41 "," 27 MinOrderQty "" 28 SQLCHAR 0 41 "\,"" 28 FOBPurchasePrice "" 29 SQLCHAR 0 3 "\"," 29 FobCurrency Latin1_General_CI_AS 30 SQLCHAR 0 41 "\,"" 30 StandardCost "" 31 SQLCHAR 0 3 "\"," 31 StandardCostCur Latin1_General_CI_AS 32 SQLCHAR 0 41 "\,"" 32 JSP "" 33 SQLCHAR 0 3 "\",\"" 33 JSPCurrency Latin1_General_CI_AS 34 SQLCHAR 0 8 "\",\"" 34 TariffCode Latin1_General_CI_AS 35 SQLCHAR 0 30 "\",\"" 35 TariffCodeDesc Latin1_General_CI_AS 36 SQLCHAR 0 17 "\",\"" 36 UPNBarcode Latin1_General_CI_AS 37 SQLCHAR 0 28 "\"\r\n" 37 EANBarcode Latin1_General_CI_AS Sample of Data to be inserted "AB", "530002 ","2 x Coffee Mugs, Lid and Spoon ", "MD" , 12.0000 ," ",12.8346,12.2047 ,9.6457 ,1510.9260 ,4.1447 ,.0000 ,.0000,.0000 ,.0000 ,.0000 ,0 ,0 ,0 ,0,.0000,0 ,"NJ","PMRTYS50","HK ","JCL ",12000.000 ,.00000 ,"HKD",.00000 ,"USD",.00000 ,"USD","00123008"," ","987654321123 ","(24)11223344556677(99)00 " "CD","530004 ","TT Crystal Bowls x 4 ", "MK" , 12.0000 ," ",12.9921,12.2047 ,11.8110,1872.8075 ,5.7320 ,.0000 ,.0000 ,.0000 ,.0000 ,.0000,0 ,0 ,0 ,0,.0000,0 ,"NJ","DHTSK50","CN ","JCL ",6000.000 ,.00000 ,"HKD",.00000 ,"USD",.00000 ,"USD","12341123"," ","123456789987 ","(23)12345678911223 (88)00 " H:\>bcp Trial.dbo.Testing in E:\Trialfiles\Sample.txt -f Test3-c.fmt -S uklesq01 ???T SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Incorrect host-column number found in BCP format-file Please help. Thanks in advance. DC

rakesh.hemraz
rakesh.hemraz

Thanks for this article. BCP really works a lot more quicker than other means especially when you have loads of data to move around.

patelvishal81
patelvishal81

hey i have my data in the following format Line 1: 000000004998152225|6783|1000|E|1110006042|000010|2006|03| 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |20060217 Line 2: F01U002255 |6783|1000|E|1110003940|000010|2006|03| 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |20060217 Line 3 000000004998151567|6784|1000|E|1110006225|000090|2006|02| 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |20060216 I wrote following script to copy this data from .txt file to table in the server [db_scpm-sql].[testing].[mska3] in "Z:\40_SCPM\80_SQL-server\80_Implementation\90_testdata\MSKA.txt" -c -t"|" -T -S FE0DBP97 but here it give me the following error. I tried with the format file option also, but it gives the same error. Starting copy... SQLState = 22005, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for ca st specification SQLState = 22005, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for ca st specification SQLState = 22005, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for ca st specification Can anybody help me with this?? Any kind of help will be highly appreciated.

granadaCoder
granadaCoder

THANKS for the article. Using it, I was finally able to get the how-to in my head and got a fixed-width-export working. bcp "SELECT TOP 50000 cast(LastName as char(50)) , cast(FirstName as char(50)) , cast(MiddleName as char(50)) , cast(Suffix as char(50)) FROM AdventureWorks2008.Person.Person ORDER BY NEWID()" queryout PeopleAreCool.txt -c -t -T -SMyServerName\MyInstanceName I couple of things from my situation. the -t (lowercase t) says "the column delimiter needs to be nothing"...where the default is a tab. The cast as char(X) is how you get the delimitation. This works in hand with the -t issue I just mentioned. The queryout means what you'd expect. Instead of hitting a base table or view, you get to write a query. Mine hits the 2008 version of the AdventureWorks database from codeplex. The order by newid() is totally unnecessary, but good for testing so you don't always have the same first row of data. The thing that was killing me that your post cleared up with the -S argument. -SMyServerName\MyInstanceName looks kinda weird, but that is the syntax sugar I was missing! Thanks again.

ertantural
ertantural

my program use sql 2000 . i want to transfer data from sql2005 to sql2000 . Is there ny program for transfering

Editor's Picks