From: ruby@...64.com
Date: 2005-09-08T03:40:02+09:00
Subject: Re: Sorted arrays

Ah, OK.  I'm finally getting this through my thick skull.  :-)  That sounds pretty cool, I wasn't aware you could do that (have it just skip the duplicates).  But, hey, I've already coded it in Ruby using a hash, which seems to work fairly well.  And this isn't the kind of thing that I'll be running every day--maybe once a month, if that.  So the Ruby solution works just fine for me.  It's a little slower than it was before, but not a lot.  Believe me, it was *really* bad the way I was doing it using an unsorted array.

Dan

From Robert Klemme <bob.news@gmx.net> on 7 Sep 2005:

> ruby@danb64.com wrote:
> > Yes, but that would sort of defeat the purpose.  The point is, I
> > don't WANT the duplicates.  Otherwise I could simply do away with the
> > unique constraint altogether and then just write something in T-SQL
> > to remove any duplicates after the data is BCP'd in.
> 
> You don't with option IGNORE_DUP_KEY (see the page I referenced): with
> this option your duplicates are simply ignored during bcp'ing.  You just
> see a warning but the import is otherwise fine.  So you actually do not
> have to preprocess your files; there will be no duplicates in the DB.
> It's different with a constraint: in that case the import will fail so
> you
> end up with no records imported into the DB.
> 
> >  But, since I'm
> > creating this .DAT file for BCP which is going to be BCP'd into quite
> > a few SQL databases, it is much better for me to just eliminate the
> > duplicates from the .DAT file.
> 
> That depends on how many DB's you are writing to - it might be more
> efficient with or without preprocessing.
> 
> > But anyway, thanks for the suggestion.  It's always good to know what
> > all the alternatives are.
> 
> Anytime.
> 
> Kind regards
> 
>     robert
> 
> >
> > Dan
> >
> > From Robert Klemme <bob.news@gmx.net> on 7 Sep 2005:
> >
> >> ruby@danb64.com wrote:
> >>> Thanks for the help.  I switched it to use a Hash, and that
> >>> performed MUCH better.  The data is not coming from any SQL
> >>> server--it is just a flat file.  The Ruby script processes it into
> >>> a format so that it can be BCP'd into MS SQL Server.  And when the
> >>> table that it gets BCP'd into has a unique constraint on the MD5
> >>> hash field--hence the need for me to eliminate the duplicate values
> >>> beforehand.  Granted, I could have changed the process of importing
> >>> the data so that this unique constraint was not necessary, but it
> >>> seemed better to me to eliminate the duplicates using Ruby in this
> >>> case.
> >>
> >> If you're on MS SQL Server there's another option: you can use an
> >> index with IGNORE DUPLICATES: that way you don't have to preprocess
> >> the file at
> >> all:
> >>
> >>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp
> >>
> >> Kind regards
> >>
> >>     robert
> 
> 
>