[dba-SQLServer] Bulk insert

And that is exactly what I am doing and it is soooooo KLUDGY!!!

I am running a program I wrote to read the data out of the original fixed
width file because SQL Server won't handle fixed width files and strip off
the spaces.  How incredibly stupid is THAT?  Is there ANYONE out there who
WANTS those spaces?  So I am already using KLUDGE to get data into SQL
Server.  Now I export it out to a perfectly valid CSV file only to discover
that SQL Server BCP and Bulk Insert don't even look at (understand) quotes
around comma delimited fields.  

But ACCESS does.  But Access is a TOY remember?  What exactly does that make
SQL Server that it needs a toy to feed it data?

This has been an exercise in discovering just how brain dead the data import
processes are (or can be anyway) for SQL Server.  This is NOT rocket
science.  I am able to write a utility to open / import / mangle / export it
back out to another file in VBA.  How tough can it be to do this import
inside of SQL Server natively?

I have no idea how widespread this kind of file is but I can tell you that
that is all I see EVER in the industry I am dealing with.  HUGE files, fixed
width, space padded right.  And I can tell you they have been a royal PITA
to get into SQL Server.

At least now I have my own utility that can get I these input files into the
format I need, even if it is in ACCESS/ VBA.  My next step is to port this
to VB.Net so that I can do it a little more "natively".  Once I get a little
more familiar with VB.Net I want to look at storing the data right into a
recordset in ADO and then write that back to SQL Server.  If that is too
slow (I suspect that it will be) then I can still do what I do now and
import / mangle / write to file and then run a stored procedure to do a Bulk
Insert from the file I create.

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 Stephen
Hait
Sent: Tuesday, May 01, 2007 11:01 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Bulk insert

I agree that BULK INSERT is faster. If quoted values in your source file are
causing problems when using BCP, one thing you might try is to import first
into MS Access which handles quoted values easily.
Then export the data from Access as tab delmited. You can then specify the
tab character as the field terminator in your BCP command with the switch,
-t"\t"

Good luck.

On 5/1/07, Eric Barro <ebarro at verizon.net> wrote:
> DTS may be more flexible but it is significantly slower than BULK INSERT.
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com