[dba-SQLServer] Bulk insert

Jim Lawrence jlawrenc1 at shaw.ca
Tue May 8 12:23:52 CDT 2007


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 




More information about the dba-SQLServer mailing list