[dba-SQLServer] Bulk insert

Uhh, there is a one line fix to remove trailing blanks in SQL, different 
defaults
for SQL Server versions and NChar and VChar.

SET ANSI_PADDING OFF

 When a table is created with the setting turned on (the default), 
spaces are
not trimmed when data is inserted into that table. When ANSI_PADDING
is off, the spaces are trimmed.

So if you SET ANSI_PADDING OFF, create your table, then set it back on
again, when you bcp the data into the table, the excess trailing
spaces will be eliminated. The only caveat here is if you have empty
fields in your file, a single space is inserted instead of a null. If
this is the case with your data file, you will need to do an update to
set columns to null when len(yourcolumn) = 0.

See BOL
http://msdn2.microsoft.com/en-us/library/ms188340.aspx

http://msdn2.microsoft.com/en-us/library/ms187403.aspx

JWColby wrote:

>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 
>
>__
>

-- 
Marty Connelly
Victoria, B.C.
Canada