[dba-SQLServer] Bulk insert

ROTFL.  One problem I have is a "big boy" database that requires a toy to
feed it data.

And yes, that is the fault of the tool.  Luckily I am an expert at
programming the "toy" so I can get around the shortcomings of the ... Hmm...
"big boy's database".

I do notice that after all my posts there is dead silence about getting SQL
Server to do what I need in an expeditious manner.  Having combed the web I
also find that there are hundreds of posts about this shortcoming of SQL
Server.

"Oh, well... You can import to a temp table and then use xxx to strip the
spaces and then use YYY to get the data where it really should have been put
in the first damned place by this "big boy's database".  Yea, except that I
have 60 of these files to do, and another 100 coming in a few weeks and
another 50 shortly after that.  It is waaaay faster to program my "toy" to
feed it data.

I will be the first to say that SQL Server is a powerful database engine,
that is why I use it.  I am also however willing to admit that it sucks in
some very visible ways.  

Learning curve my hind ass.

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 David Lewis
Sent: Tuesday, May 01, 2007 1:29 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [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


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