Refreshing a table using CREATE TABLE, DROP and Rename

Question

Refreshing a table using CREATE TABLE, DROP and Rename

Ricardo B

Grasshopper

Points: 22
More actions
October 28, 2015 at 1:08 pm

#322211

A read-only database (database1) is updated via log shipping every minute,
then a procedure queries multiple tables and refreshes a large table on database2.
😉
For this process I
1) run an expensive query agains database1 and generate a new Table (TempTable_A) in database2
2)
BEGIN TRAN
DROP TABLE Table_A
RENAME TempTable_A to Table_A
COMMIT
Does it look too ugly :w00t: ? Otherwise I would have to create very complex verifications and updates. :crazy:
Suggestions to minimize locks? 😀

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323506 More actions · Answer 1

i do something very similar, but i rename the original table just in case, along with any indexes/constraints/defaults/foreign keys.

then rename the temp table, and it's indexes/constraints/defaults/foreign keys to match the expected definition.

if you use random constraint names, you don't need to go to the trouble, but when i use something like redgate to compare, we have naming conventions and specific names, so we like it to remain constant.

--rename the old! table and all constraints

EXECUTE sp_rename 'HospLogDetail','HospLogDetailx'

EXECUTE sp_rename 'PK_HospLogDetail','PK_HospLogDetailx'

EXECUTE sp_rename 'DF_HospLogDetail_IsActive','DF_HospLogDetail_IsActivex'

EXECUTE sp_rename 'DF_HospLogDetail_IsDeleted','DF_HospLogDetail_IsDeletedx'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ricardo B Grasshopper Points: 22 More actions · Answer 2

That's a good idea, renaming the original will probably make the transaction commit quicker and I can drop the objects later.

tks

I just hope more people comment on the topic.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

I have the same situation, but I don't rename the original. I create the temporary table and populate it, drop the original and rename the temporary one to the original name. It takes the downtime created by a 30-ish second build process down to 53 ms, which is acceptable to the business.

The only downside I know of is that the space is consumed by both tables until I fire the sp_rename, but that's okay with me.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions