[dba-SQLServer] Bulk insert

John:

There are a few websites concerned with sql server, with active forums.
I recommend you check them out.  One is sqlcentral.com, the other is
sswug. 

You are right, access is a 'toy' when compared to sql server.  One
problem you are having is that you don't have the luxury of a learning
curve -- but that is not the fault of the tool.  Hang in there.  D


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