CODEWITHSUNDEEP
sqlsql-server
glenho123
glenho123

Reputation: 577

Drop and recreate table (locking)

I need to update a large set of data into a SQL Server table which I use for caching and this happens about every 10 minutes.

I find that rather than using "Insert into ...tableName" that dropping the table and recreating it with "Select * INTO ...tableName" is a lot faster.

My question is though how do I lock the table i.e. prevent users querying the table whilst it is dropping or selecting into?

If I wrap it in a transaction does this work?

Additional Just clarifying this further as I probably wasnt clear enough to start with.

I was originally using TRUNCATE and then INSERT INTO but this was taking around 8 - 10 seconds. Which potentially can hold users up.

So what I wanted to do instead was to drop the table and then recreat the table with SELECT INTO which was very much faster. However, I guess my question was what happens if someone queries the table after the DROP and before the SELECT INTO? What happens in this instance?

Upvotes: 2

Views: 2597

Answers (1)

Fabien
Fabien

Reputation: 4972

An option for you is to load your data into a table with temporary name, then quickly DROP your main table and RENAME the other.

Those are quite fast operations (atomic?), the unavailability should be minimal.

https://dev.mysql.com/doc/refman/5.7/en/rename-table.html

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

All you have to do is, for the very few users who will need to read the DB during the process, to show them a "please wait message" or similar, so that the user experience stays smooth (use a flag to track when the process starts/finish).

Upvotes: 1

Related Questions