Bulk replace of content of SQL Server tables

bulk-load sqlbulkcopy sql-server

Question

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.

Popular Answer

Load them into temporary tables and MERGE (merge statement) Them into the daily use tables.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why