sp_rename advice pls

Gregory Bond · *nix forums beginner Joined: 11 Apr 2005 Posts: 21

We have an application that has a large, mostly read-only, table. This
table is created by bcp-ing in a bunch of data then doing some fairly
heavy duty SQL manipulation.

The kicker is the update has to happen while the app is still running,
and we have been having a fair bit of grief with app timeouts while this
table is updated.

So we are considering using sp_rename like this:
bcp data into table_new
sql to fix up all the odds and ends in table_new
begin transaction
sp_rename table, table_old
sp_rename table_new, table
end transaction

Does this sound like a good idea? Anything we should watch out for?

[There are no triggers, rules or stored procs that reference this table,
it's all done from ct-lib SQL. But it does have a number of somewhat
complex indexes.]

Will sp_rename work in a transaction like this? (We can live without the
transaction I suppose - better than the grief at the moment!)

Anthony Mandic · *nix forums Guru Wannabe Joined: 05 Jul 2005 Posts: 164

Gregory Bond wrote:

Google