[dba-SQLServer] Bulk insert

JWColby jwcolby at colbyconsulting.com
Wed May 9 06:42:09 CDT 2007


>Occam's Razor

ROTFLMAO.  Occam's razor FOR THEM!!!  Pretty much useless to me.  Of course
not being an "SQL guru" I am going to be told that this is really "the best
way", and "it's not the fault of the tool" and a bunch of other platitudes
and excuses for why I would be given tools that I cannot use, and of course,
not being an SQL Guru I will be in no position to protest.

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
MartyConnelly
Sent: Wednesday, May 09, 2007 2:23 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Bulk insert

Occam's Razor

JWColby wrote:

>>The only caveat here is if you have empty fields in your file, a 
>>single
>>    
>>
>space is inserted instead of a null.
>
>What is it with all "the only caveat here" stuff?  I am sure that there 
>is a darned good reason.
>
>In the end it is just easier to roll your own rather than work around 
>the issues that the built in stuff seems to have.  I have 150 fields 
>(in this data set).  Somehow I have to do an update on all 150 fields.  
>I suppose I could have my converter run 150 update queries to do each 
>column.  Or 700 update queries to do the next data set.  Or just do the 
>stripping of the spaces external to SQL Server and be done with it.  
>Either way I still have to use my toy.
>
>Once I move up to VB.Net I will be able to use threads to do the 
>stripping and the BULK INSERT Sproc in parallel.
>
>BTW, I have to do something very similar all over again once I get the 
>data in.  I will need to export the entire table back out, 2 million 
>record sets of data to delimited files for CAS / NCOA processing, 
>dumping 100 million records out into ~50 files (just the address data 
>this time).  The CAS / NCOA process theoretically will process all 
>files placed into an input directory (input to that program), dumping 
>the processed files into an output directory (output from that 
>program).  At which point I have to pull all of the CASS / NCOAd files 
>BACK out of that output directory into to yet another table.  And that is
just the "pre-processing".
>
>You might be getting a clue by now why I do not want to be manually 
>doing all the crapola involved with the solutions that do not involve 
>an external control process.  Someday fairly soon I will have a 
>completely automated system for doing all this.  I will be back to 
>blowing bubbles and poking at Charlotte with a big stick.
>
>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 
>MartyConnelly
>Sent: Tuesday, May 08, 2007 10:27 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [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
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>  
>

--
Marty Connelly
Victoria, B.C.
Canada

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




More information about the dba-SQLServer mailing list