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

<<<
then do the 
entire thing in memory, updating the first record with 
only "data containing fields"
>>>
John,

I suppose that MS SQL is "smart enough" to do that "dirty work" for you - if
you update a field with the same value it has in the case the updating value
is NULL or (NULL or EMPTY string for char data) as it's done in SP I
outlined then MS SQL will not make that field update at all...

<<<
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.
>>>
...you can do all that in one SP, which I have got outlined in my previous
posting

<<<
This might be a good thing to do in C# to learn ADO stuff.
>>>
Yes, but that would be rather simple (although large because of 640 fields)
function, which will use:

SqlConnction,
SqlCommand and
SqlDataReader

Remark: as I mentioned previously you can use ROW_NUMBER() T-SQL function to
organize getting records' batches to be processed, say 1000 or at a time,
and getting that batches can be made as a multi-threaded function... 

<<<
Relatively speaking, this would no doubt FLY.
>>>
...then it could fly, although I can't say for sure because "flying program"
is a relative term heavily depending on one's point of view/experience :)

--Shamil  

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, September 22, 2009 6:30 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [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


 

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4447 (20090922) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru