Compare Disparate Data

We currently have a process where we receive an inventory file from a vendor every hour. We need to compare that inventory data in the file to what we currently have in our DB. Any thoughts/ideas on how yo开发者_如何学运维u would approach this?

Here is what we are doing currently:

We pull the data from the db table into C# and do the same with the file then use LINQ to compare.

We had originally been staging the file data into a separate db table then comparing.

There can be up to about 100,000 records in the file.

Thanks in advance.....

S

You should use SSIS. As a data flow engine, it has all sort of optimizations around the whole process of reading, processing and writing data that your application will miss. See Considerations for High Volume ETL Using SQL Server Integration Services for an introductory discussion on the topic.

Can the vendor include a last updated datetime field in the inventory file? It would be a lot simpler to only process changes than iterating through 100,000 records.