From: Robert Klemme <bob.news@...>
Date: 2005-09-07T23:56:28+09:00
Subject: Re: Sorted arrays

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