April 29, 2008 at 8:46 am
I have demographic data in several tables in production with IDKey being the unique identifier. Daily data loads include new records and updates. Basically what I need to do is :
1. Backup the target rows by copying them into a table using a join to the source table on IDKey to preserve the "before" version until the next load.
2. Delete those rows from the target table using the same join.
3. Insert the source data using a left join from the source data to the backup data on IDKey.
Bottom line: For IDKeys that already exist, 25 - 30% of the fields will come from the source data, the rest from the target data. For existing IDKeys, the logic in the CASE statement will be WHEN ISNULL(source value, xx) <> ISNULL(target value, xx) THEN source value ELSE target value.
Will this be more or less effective than a simple insert followed by an update?
April 29, 2008 at 9:23 am
Kevin Durham (4/29/2008)
I have demographic data in several tables in production with IDKey being the unique identifier. Daily data loads include new records and updates. Basically what I need to do is :1. Backup the target rows by copying them into a table using a join to the source table on IDKey to preserve the "before" version until the next load.
2. Delete those rows from the target table using the same join.
3. Insert the source data using a left join from the source data to the backup data on IDKey.
Bottom line: For IDKeys that already exist, 25 - 30% of the fields will come from the source data, the rest from the target data. For existing IDKeys, the logic in the CASE statement will be WHEN ISNULL(source value, xx) <> ISNULL(target value, xx) THEN source value ELSE target value.
Will this be more or less effective than a simple insert followed by an update?
I suspect the simple insert & updates will be better. Either way, I'm looking at the CASE statement with some concern. I think you'd be better off using a simple COALESCE to get those values back out of the system. Although I might be misunderstanding based on that code snippet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2008 at 9:49 am
I wish I could use ISNULL(target, source) or COALESCE(target, source). That would make my task easier. Unfortunately, I am not only checking for NULL values in the target. The target could have an outdated value that needs to be replaced. For instance, a person could have a new home phone or new address.
April 29, 2008 at 10:32 am
OK.
Then without details... I think the insert & update is better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply