[dba-VB] SPAM-LOW: Re: Merge rows

 >I assume you have a unique natural (alt-)key, say, FirstName, LastName (of course you'll have some 
more fields in this key:

Even better I have a HashPerson field which is a unique identifier based on FName/LName/Addr/Zip5/Zip4.

I assume your code is comparing fields values in a loop.  Yep, that is what has to go on.  I was 
going to do a SQL update but an in-memory operation would probably be optimum.

What would REALLY be optimum wold be to read the set of records into an ADO recordset, then do the 
entire thing in memory, updating the first record with only "data containing fields" from the 
subsequent records.  Then when the entire recordset had updated that first record, write the first 
record back out, perhaps even to a new table.  Write the PKIDs from the now "unneeded" or 
"duplicate" records to a table to use in a delete query at the end.

Relatively speaking, this would no doubt FLY.

This might be a good thing to do in C# to learn ADO stuff.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Hi John,
> 
> I suppose you can use static (compiled) SP - I assume you have a unique
> natural (alt-)key, say, FirstName, LastName (of course you'll have some more
> fields in this key:
> 
> Procedure MySP
> (
>   @firstName nvarchar(50),  
>   @lastName nvarchar(50),
>   ---
>   @f1 int,
>    ...
>   @fn nvarchar(80)
> )
> 
> 
> set nocount on
> declare @testCount int
> select @testCount = count(*) from MyTable where FirstName = @firstName and
> LastName = @lastName
> 
> if (@testCount = 0)
> begin
>   -- insert
> End
> 
> else
> 
> begin
>  update myTable
>     set 
>         f1 = IsNull(@f1, f1), 
>         ...
>         fn = case (isnull(len(@fn),0)) when 0 then fn else @fn end
>         where FirstName = @firstName and LastName = @lastName
> end
> 
> set nocount off
> 
> Try.
> 
> --
> Shamil