Replacing a live table while in use.

[Cairnsey]

Morning All,

I want to create a temporary table while an original is still in use. Once created I want to replace the original with the temporary and continue business as usual. This process is also going to be a weekly occurance. The only bonus in this affair is the fact that all users will only have read access on either of these tables.

I can't see it just being as straight forward as deleting the original and renaming the new, especially without wrecking havoc upon all those currently looking at the table.

Any ideas or has anyone done anything similar before ?

 

[SQLBill]

I don't think you'll be able to do it without creating SOME havok on users.

Eventually you have to drop/delete/remove the table they are using.

Is your database used 24/7?

If not, pick a time when it's not being used and do the replacement then.

If it is, pick a slow day and time, tell your users you are taking the system down for a short while and replace it then.

-SQLBill