Discussion on:

How do I use BCP in SQL Server?

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

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.

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.

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

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

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