March 10, 2011 at 2:46 am
Hi there all,
I have a table stucture as:
location, month, metric1, metric2,..metric100
I want to update the rows in this table with newer data, which is in the same format as above. The combination of month and location becomes the key.
1) So we can insert rows from the newer table if there is no key match.
2) Where there is a match on this key, we need to replace the metric values.
Can anyone suggest a neat way of doing this please ?
Regards, Greg.
March 10, 2011 at 2:59 am
Look at using the Binary_checksum() function in SQL for all columns but the Business Key and meta data columns.
So
Update
Destination
Set
Col1 = Source.Col1
:::::
From
DestTable AS Destination
JOIN SourceTable Source ON Source.BusnessKey=Destination.BusinessKey
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 10, 2011 at 3:03 am
Look at using the Binary_checksum() function in SQL for all columns but the Business Key and meta data columns.
So
Update
Destination
Set
Col1 = Source.Col1
:::::
From
DestTable AS Destination
JOIN SourceTable Source ON Source.BusnessKey=Destination.BusinessKey
Where
Destination.Binary_Checksum(Col1,Col2,Col3........)<>Source.Binary_Checksum(Col1,Col2,Col3........)
There can be a degree of Collision and Decimal columsn that change by a factor of 10 (10.1 and 101.00 are the same) dont show up in the algorthim.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 10, 2011 at 4:07 am
Thanks to you for responding. I am a newbie however, and wonder if a more worked example based on my dataset could be provided - possibly combining both techniques mentioned - if anyone out there knows how to do this ?
Regards, Greg
March 10, 2011 at 4:21 am
greg.bull (3/10/2011)
Thanks to you for responding. I am a newbie however, and wonder if a more worked example based on my dataset could be provided - possibly combining both techniques mentioned - if anyone out there knows how to do this ?Regards, Greg
It's a bit difficult without knowing the exact structure of your source and destination tables. Hopefully the following can help.
MERGE Destination AS D
USING Source AS S
ON (Source.BusnessKey=Destination.BusinessKey )
WHEN NOT MATCHED
THEN INSERT(....) VALUES(...)
WHEN MATCHED
THEN UPDATE SET ... = ...
March 10, 2011 at 4:23 am
I mean, do I have to list all those 100 metric fields explicitly, to insert or update ?
Can I try something like INSERT VALUES (B.*) !?
OR
UPDATE SET A.* = B.*
I don't need to update my key fields though...
Greg
March 10, 2011 at 7:18 am
By the way, I can't us MERGE, need a SQL2005 solution.
March 10, 2011 at 12:05 pm
greg.bull (3/10/2011)
By the way, I can't us MERGE, need a SQL2005 solution.
Why did you post this in the SQL2008 forum? Try posting this in the SQL2005 forum.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply