[dba-SQLServer] Bulk insert

Jim,

At this point it is not necessary.  I built a preprocessor in a few hours
using my toy (Access).  My toy application handles everything exactly as
described.  Someday (soon I hope) I will port that to VB.Net which I hope
will be much quicker in the preprocessing department.  Then I will be
considered by some as being a real man, playing with real tools.  ;-)
Others will still consider me a child, playing with toys because I didn't
take it straight to C#.  SOMEDAY (far in the future) perhaps I will embed
those pieces directly in CLR programming inside of SQL Server 2005.  Then I
will be able to look down my nose at those children still playing with toys.

For now, it works and with the addition of driving the Sproc from the vba
will be an integrated application like what I described. 

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 Jim
Lawrence
Sent: Tuesday, May 08, 2007 1:24 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Bulk insert

Hi John:

As to your query:

<comment>
The issue is not "handling fixed width" but in stripping off the trailing
spaces in the process.  The environment that I work in isn't "import a file
and use it", but rather "import 56 files containing a HUNDRED MILLION
records of 150 (or SEVEN HUNDRED) fields into a single table and use it".
The SPACES in this last file were more than 40% of the total volume of the
file.
</comment>

The only way to pre-handle the data is to build a DTS/SSIS application. I
wrote a reply last week suggesting this pre-processing method. It will
definitely work, as the data is being inserted. The speed is slower that
bulk-insert. I am only really familiar with the old DTS but the new SQL 2005
has a legacy section that supports this functionality. 
Can send some sample code if you are interested.... It's in basic VB 6
format so any amount of VBA logic can be added and then it can be compiled
for extra performance.

Jim 

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