[dba-SQLServer] BCP

John,

Havn't done it myself but BOL says...

Using Format Files
When bulk copying data using interactive mode, the bcp utility prompts
you to store information regarding the storage type, prefix length,
field length, and field and row terminators. The file used to store the
format information for each field in the data file is called the format
file:

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

Although the default name for the format file is Bcp.fmt, a different
file name can be specified.

This format file provides the default information used either to bulk
copy the data in the data file back into an instance of Microsoft(r) SQL
Server(tm) or to bulk copy data out from the table another time, without
needing to respecify the format. When bulk copying data into or out of
an instance of SQL Server with an existing format file, bcp does not
prompt for the file storage type, prefix length, field length, or field
terminator because it uses the values already recorded.

To use a previously created format file when importing data into an
instance of SQL Server, use the -f switch with the bcp utility or the
FORMATFILE clause with the BULK INSERT statement. For example, the
command to bulk copy the contents of New_auth.dat data file into the
authors2 table in the pubs database using the previously created format
file (Authors.fmt) is:

bcp pubs..authors2 in c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa
-Ppassword


So, create an out script based on your table, 400 fields (wow), save the
format file it generates.
Use that for the import...  How hard could it be...lol

cheers 


Michael M


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Thursday, 10 August 2006 11:42 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] BCP

>You could also look at bcp to import your files, it should be faster.

These files come to me in a zip file (21 of them), unzipped they are
each exactly 9,706,055 bytes (just under 10 gigabytes).  Each contains
exactly 3 million records.  Each record contains some 400+ fields, fixed
width but comma delimited.  The FIRST file has the header containing the
field names etc, but the remaining files do not.

Because of the size of the files, they are a pain to do anything with
programmatically (VBA etc).

BCP is a command line process correct?  I would love to do anything to
speed this up but basically the import wizard, while cumbersome to use
over and over, make it drop dead simple to get these imported.  IIRC BCP
(which I have never used) requires a format file?  Can I get SQL Server
(EM?) to create the format file for the table given the existing table
(which I have).  I sure don't want to 

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael
Maddison
Sent: Wednesday, August 09, 2006 9:33 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SQL Server hanging

John,

Yes SQL will stop and 'think' in situations like yours  :-) Stopping SQL
when it is busy is almost always a bad idea.
It will attempt to recover, roll back etc when you restart it again as
you discovered.

IIRC you can configure DTS to log any errors.
You can also specify that each package operates within a transaction.
1 fails all fails and rolls back, which is probably what was happening
when SQL stopped responding.
It takes a while to roll back a large number of transaction.  I have
learnt to give it the benefit of the doubt and 99/100 it will eventually
roll back and then display whatever the problem was with the package.  

Making the data chunks smaller is also a good idea.

You could also look at bcp to import your files, it should be faster.

cheers

Michael M

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com