bcp help

[ptheriault]

I have a used the bcp command to copy 4 millions rows out of a table to a .dat file. I zipped it and coppied it to another server that I am going to bcp the data back in. The database and table names and schemas are all the same. When I try to bcp the data back in it asks me to define each field. Is there are way around this.
This is the syntax I am using.

bcp mavresource.dbo.cumulative_claim in d:\dba\ce.dat -Usa -P** -SMavStg

- Paul
- Database performance looks fine, it must be the Network!

 

[SQLDenis]

did you try adding the -c -t flags?

bcp mavresource.dbo.cumulative_claim in d:\dba\ce.dat -c -t , -r \n -Usa -P** -SMavStg


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

Thanks Denis. Those flags did the trick.

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

do you know what each flag does?


- Paul
- Database performance looks fine, it must be the Network!

 

[SQLDenis]

yes

Bulk copy option bcp utility switch BULK INSERT clause 
Character mode format -c DATAFILETYPE = 'char' 
Field terminator -t FIELDTERMINATOR 
Row terminator -r ROWTERMINATOR

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

thanks again Denis

- Paul
- Database performance looks fine, it must be the Network!

 

[SQLDenis]

np


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

one more question Denis.
What does the \n do?


- Paul
- Database performance looks fine, it must be the Network!

 

[SQLDenis]

newline


-r row_term

Specifies the row terminator. The default is \n (newline character). Use this parameter to override the default row terminator.


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

For some reason the copy starts then just stops with the following..

0 rows copied
Network packet size: 4096
clock Time (ms.) : 53578

It's not importing the data?

- Paul
- Database performance looks fine, it must be the Network!

 

[SQLDenis]

what does the data look like?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

Here is an example of one row

WC0001244-02 141374731012004 2000-01-26 00:00:00 2001-01-26 00:00:00 2000-11-30 00:00:00 35513 AR 750.0000 633.1500 3000.0000 2540.9600 161.2300 191.0000 356.2700 422.0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 -19.4100 -23.0000 .0000 .0000 .0000 .0000 .0000 .0000 135.0600 160.0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 24011 .0000 .0000 REN 1018043402 285761


- Paul
- Database performance looks fine, it must be the Network!

 

[SQLDenis]

This is not a tab delimited file

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

I used bcp out like this.

bcp mavresource.dbo.cumulative_claim out ce.dat -Usa -P** -SServername

Should I have used a flag with this for format?


- Paul
- Database performance looks fine, it must be the Network!

 

[SQLDenis]

use this one for out

-c

Performs the bulk copy operation using a character data type. This option does not prompt for each field; it uses char as the storage type, no prefixes, \t (tab character) as the field separator, and \n (newline character) as the row terminator.


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

Thanks Denis,
I'm going to go home and continue to work on this.
it's 5 O'clock...
Time to crack open a beer!


- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Denis,
Thanks for your help today. This Buds for you! I got the BCP to work.

- Paul
- Database performance looks fine, it must be the Network!