My ASP.NET MVC application has a number of web servers accessing a SQL Server database via Entity Framework 6. The database has 2 tables, with a one to many relationship between them.
Once a day, the entire contents of both tables needs to be replaced by a new dataset that is loaded over the Internet from a remote web service. The number of records may be in the tens out thousands. The records are not very big, with about 10 nvarchar and integer fields each.
I'm planning to have one web server load the dataset from the remote web service into the 2 SQL Server tables. It would need to remove the old content and efficiently load the new content. While this is happening, the other web servers have to be prevented from accessing the tables (probably by locking the tables).
I'm looking for fast options to accomplish this, and their pros and cons. If there is a NuGet package or Entity Framework command that does this for me, that would be ideal.
Load them into temporary tables and MERGE (merge statement) Them into the daily use tables.