Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I have a database having a staging_table. From this table, entries are added to other table named Profile. Whenever staging_table is updated, it checks whether all its entries are present in Profile, if any is not present, add it to profile. Till, here its fine. Now I want that even if an entry exists in Profile, there might be a condition where some fields in Profile have been changed. So also want those entries to get updated.

Requirement: I cannot use any flags or any other table to check for its update.

Should I compare all fields? But that is very in-efficient. Please tell me, what should be the most efficient method that I should follow to achieve this?

Thanks

share|improve this question
Which RDBMS are you using? – Phil Jul 4 '12 at 6:24
I am using SQL Server 2008. – Raj Wadhwa Jul 4 '12 at 6:27
1  
hi there , i think you should try it with merge. for more information take a look here kodyaz.com/articles/… – ElDoroCapitano Jul 4 '12 at 6:33
@BenediktSchackenberg Thanks for the reply. I have 15 fields in Profile Table & 25 fields in Staging Table. Any of the field may get change in the process, so , do I need to compare all fields in the ON Clause of the MERGE statement? – Raj Wadhwa Jul 4 '12 at 6:51
1  
In that case, it is like DELETE the record and re-insert it. Not so efficient I guess. – Raj Wadhwa Jul 4 '12 at 7:54
show 4 more comments

1 Answer

up vote 0 down vote accepted

To make an efficient compare of all columns between 2 tables, you could use a UNION method with GROUP BY as described in Diff2Tables. Alternatively, if your profile table can have rows deleted from it without cascade delete, you could, drop indexes that are not for PK of the profile, Delete rows having the same key in both tables, and insert the rows from the other table into the profile then re-build the indexes on the profile table again. This may sound bad but in fact it is not that bad. This is a typical scenario in Data Warehousing.

share|improve this answer
Thanks for the link. The solution worked. Thanks :) – Raj Wadhwa Jul 4 '12 at 11:40
Thank you for your feedback. Good luck. – Emmad Kareem Jul 4 '12 at 12:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.