BCP Format File

BCP Format File

am 25.02.2005 23:12:25 von Query Builder

I am new to BCP. Can anyone help me understand this? I tried searching
the BOL but it doesnt show much help on syntex. Lets say I have a
simple table and want to out put the records into a file. Then with
that output file, I want to create a format file using the BCP utillity
and builk insert it into another table. Can someone explain me the
syntex involved in this.. No need to go so elabrate.. Just give me a
basic senario and just the syntex....

Thanks...

Re: BCP Format File

am 26.02.2005 19:10:54 von prandal

BOL does have a bunch of topics linked off the main 'BCP' or 'BULK INSERT'
topics - these are also available on MSDN (just search for BCP). I've also
included a link to a whitepaper below that has a bit more detail on usage
scenarios you may be interested in.

http://www.microsoft.com/technet/prodtechnol/sql/2000/mainta in/incbulkload.mspx

Also if you just enter 'bcp' at the command prompt , bcp will list its
usage. An example (given a database called foo with a table t1 (c1 int, c2
int))

C:\seam\sql\ntdbms\storeng\dmu\dbcc\source>bcp foo..t1 out c:\t1.dat -T

Enter the file storage type of field c1 [int-null]:
Enter prefix-length of field c1 [1]:
Enter field terminator [none]:

Enter the file storage type of field c2 [int-null]:
Enter prefix-length of field c2 [1]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n]
Host filename [bcp.fmt]: c:\bcp.fmt

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 30 Average : (33.33 rows per sec.)

C:\seam\sql\ntdbms\storeng\dmu\dbcc\source>

And then to bulk insert in:

C:\seam\sql\ntdbms\storeng\dmu\dbcc\source>osql /E
1> use foo
2> go
1> create table t2 (c1 int, c2 int)
2> go
1> bulk insert t2 from 'c:\t1.dat' with (formatfile='c:\bcp.fmt')
2> go
(1 row affected)
1>

Regards

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Query Builder" wrote in message
news:1109369544.975842.308250@f14g2000cwb.googlegroups.com.. .
> I am new to BCP. Can anyone help me understand this? I tried searching
> the BOL but it doesnt show much help on syntex. Lets say I have a
> simple table and want to out put the records into a file. Then with
> that output file, I want to create a format file using the BCP utillity
> and builk insert it into another table. Can someone explain me the
> syntex involved in this.. No need to go so elabrate.. Just give me a
> basic senario and just the syntex....
>
> Thanks...
>

Re: BCP Format File

am 26.02.2005 21:41:29 von John Bell

Hi

You will only need to use a format file if you want to manipulate the data
in your output file in some way.
If your tables have the same format then to output the data in to a file:
bcp "Northwind..Customers" out "Customers.txt" -n -S Server1 -U"Jane
Doe" -P"go dba"

To populate the table (with the same name) into Tempdb on Server2 using a
trusted connection
bcp "Tempdb..Customers" in "Customers.txt" -n -S Server2 -T

If you only want to extract a subset or possibly change the order, you could
either limit the output using the queryout option
bcp "SELECT CustomerID, CompanyName, ContactName FROM Northwind..Customers"
queryout "Customers.txt" -n -S Server1 -U"Jane Doe" -P"go dba"

If you want to use a format file it is sometime useful to one using the
format option and then work with that. Look at the section "Using Format
Files" and that is clear on how to create/change them.

For example:
bcp "Northwind..Customers" format Customers.txt -f Customers.bcp -n -T -S
(local)
Produces the format file Customers.bcp:
8.0
11
1 SQLNCHAR 2 10 "" 1
CustomerID Latin1_General_CI_AS
2 SQLNCHAR 2 80 "" 2
CompanyName Latin1_General_CI_AS
3 SQLNCHAR 2 60 "" 3
ContactName Latin1_General_CI_AS
4 SQLNCHAR 2 60 "" 4
ContactTitle Latin1_General_CI_AS
5 SQLNCHAR 2 120 "" 5
Address Latin1_General_CI_AS
6 SQLNCHAR 2 30 "" 6 City
Latin1_General_CI_AS
7 SQLNCHAR 2 30 "" 7 Region
Latin1_General_CI_AS
8 SQLNCHAR 2 20 "" 8
PostalCode Latin1_General_CI_AS
9 SQLNCHAR 2 30 "" 9
Country Latin1_General_CI_AS
10 SQLNCHAR 2 48 "" 10 Phone
Latin1_General_CI_AS
11 SQLNCHAR 2 48 "" 11 Fax
Latin1_General_CI_AS

To create a customers.txt file with data in it using this format file:
bcp "Northwind..Customers" out Customers.txt -f Customers.bcp -T -S(local)

If I create a Customers table in tempdb then I can input the data using the
command

bcp "Tempdb..Customers" in Customers.txt -f Customers.bcp -T -S(local)

HTH

John

"Query Builder" wrote in message
news:1109369544.975842.308250@f14g2000cwb.googlegroups.com.. .
>I am new to BCP. Can anyone help me understand this? I tried searching
> the BOL but it doesnt show much help on syntex. Lets say I have a
> simple table and want to out put the records into a file. Then with
> that output file, I want to create a format file using the BCP utillity
> and builk insert it into another table. Can someone explain me the
> syntex involved in this.. No need to go so elabrate.. Just give me a
> basic senario and just the syntex....
>
> Thanks...
>