What is the best way to update an table (500 rows) using a huge master table (250,000 rows)?

I have a small table of about 500 rows with about 10 columns (using SQL Server 2005). I need to refresh its data on a weekly basis with a master table of about 250,000 rows with about 30 columns. I would like to display the fields that are different on the ASP.NET page. Although I can come up with a way to do this using C sharp in my ASP.NET page, I am interested in the best way to design such a solution.

I plan to read the entire table of 500 rows into memory, gather all the primary keys, then retrieve in memory all the corresponding rows in the master table using those primary keys, then iterate through each of the 500 rows to find the corresponding master row and make a field by field comparison and display any differences and implement any changes. Finally, I would like to write out any changed rows back to the small table. Thinking this through, I suppose my real question is what kind of technology/command should I use to store the bables in memory and write the rows (or table) back to the database? (e.g. DataReader, DataTable, Dataset, DataAdapter, etc.). Is there a simple way to write the entire dataset/database in memory back to the small table, or should I update each individual row and only those rows that will change? I expect that less than10 rows will change on a weekly basis.
dwoolley3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
Why not write an UPDATE query so everything is done in the SQL Server, you have primary keys defined so you can easily get the matching rows between the two tables.
0
dwoolley3Author Commented:
That is a good thought, AndyAinscow. If I did not have to manipulate two fields from the master to produce one field on the small table (in code), I could do that. Also, I would like to display the fields (in code) that are different so that I know what is changing. I will ponder your suggestion further, yet welcome comments on what I can use in the code.
0
AndyAinscowFreelance programmer / ConsultantCommented:
In a query you can put multiple fields from a source together to produce one field for a target.

Changes:  You could use 'two' queries.  You could make one query to filter all records that are the same (multiple joins between the tables) then exclude just those records from a further query that displays the records from the child table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>I plan to read the entire table of 500 rows into memory<<
This is simply not scalable, don't even think about it.  You need to go with a simple UPDATE statement as suggested previously.  In addition I would use the OUTPUT clause to show the original values and the ones that changed.  On a well designed table, this should complete in under 5 seconds, but probably a lot faster.
0
dwoolley3Author Commented:
Since both of you are truly experts and provided an alternative way of obtaining the results, I am giving both of you credit for the solution. Yet, I was able to accomplish my desired task in an efficient manner using C# coding. The results appear within one second of clicking a button. I needed to perform logic on some fields to produce another field and the master table was not easily accessible in my SQL Management Studio, but I did have the correct connections in the code to access the table. Anyway, I was ultimately looking for guidance on the best way to code it (among the available commands that could be used in C#). I do appreciate your expert comments on using a simple UPDATE statement (with an OUTPUT clause) and I will keep that in mind for the future. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.