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