December 13, 2006 at 9:34 am
I need to refresh a production table. If I run the following code, is there any chance that a user will see the table as being empty during the copy?(that would be bad) I would guess no, but I need to be sure. You can assume that tble2 has the same structure and has rows in it.
BEGIN TRANSACTION
DELETE FROM tble1
INSERT INTO tble1 SELECT * FROM tble2
COMMIT TRANSACTION
go
Do I even need the BEGIN-COMMIT trasaction statements if there isn't a GO betweent he delete and insert?
Thanks in advance to any responses to this post.
December 13, 2006 at 9:43 am
If you are wrapping this inside of a transaction, the user will not see the table as empty (in fact, the user will not see anything as both tables will be locked and thus any SELECTs on those tables will be blocked until your transaction is committed.
You need to consider how big these tables are. If they are too big, you will have a very large transaction and block all processes that use those objects for a long time.
If this is something you're going to do fairly often, why don't you consider having a view between the applications and the tables. This will allow you to refresh tables behind the scenes without any blocking.
In brief. You have a view called tble1 which points to tble1A.
When you need to update tble1A you update the source code of the view to point to tble1B (which has already been updated) then you have an instant swich and your process can then update tble1A when it is required and swich again.
I'm sure I didn't explain that very well, please let me know if you have any questions.
SQL guy and Houston Magician
December 13, 2006 at 10:53 am
The tables are small (less than 10,000 narrow rows) and the delete/insert takes less than a sec or two. I just wanted to hear that the table would be locked during the insert.
I do like your idea about modifying the view to point to a different table during the insert. I will keep that in mind if the BEGIN/COMMIT table lock causes problems.
Thanks for your quick reply. I figured that the user would never see an empty table if enclosed in BEGIN/COMMIT, but I just wanted someone else to verify it for me.
December 13, 2006 at 11:15 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply