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.