2012年3月8日星期四

BCP Format File

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...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" <querybuilder@.gmail.com> wrote in message
news:1109369544.975842.308250@.f14g2000cwb.googlegr oups.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...

没有评论:

发表评论