December 13, 2010 at 3:18 pm
Hey Guys
I have a table with 3 columns and 200 odd rows. I would like to truncate all the rows in this table and add a new bunch of rows. However, this table is constantly in use by a 3rd party application and I can't even run a select on this table without using the (nolock) hint.
Is there anyway, I can drop this table and create a new one without taking the application or the database offline?
Thanks
Anish
December 13, 2010 at 5:43 pm
No, but you can minimize the impact. Create a new table with the schema you need, and load it. Then swap the tables with two calls to sp_rename. (Existing to table_old. table_old to existing).
Make sure to watch the Primary Key's constraint name, you can't duplicate it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 14, 2010 at 4:48 am
If the table is heavily used, you might have problems in obtaining a schema lock to do the sp_rename.
You may have to look at doing an upsert and changing the minimum amount of data possible. It may also be worth contacting the third party application provider to see if the application can use one of the snapshot isolation levels.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply