sp_rename

Question

sp_rename

Grasshopper

Points: 12

April 12, 2005 at 5:48 pm

Hi, I have a table with around 2.3 million rows in it. Every so often I need to replace all the rows in this table with a new set of values that are sent to me in a csv file. To do this I am using a bulk insert into a temporary table and then switching the temporary table with the live table by using sp_rename. Unfortunately, the trigger and indexes on the original table no longer work. Does anyone know how I redefine the dependant triggers, indexes etc, to work on the new table? Thanks,

Shaun

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

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

The triggers and indexes are pre-defined. Use drop/create method. Indexes creation could take time, it worths the waiting to reflect the new reality.

shaun bethell Grasshopper Points: 12 More actions · Answer 2

Hi,

I was originally going to do that but the problem is I am creating a set of config tools to analyse, validate and process changes to the database. The database owners may decide to alter the triggers and indexes in the future and I add drop and create scripts, it means they cannot make such alterations.

Also, everything has to be done within stored procedures so the only way to create the trigger (which is truly huge) is;

declare @xxx varchar(7000)

set @xxx = "CREATE TRIGGER IU_POSTCODES...."

exec (@xxx)

It works but it's pretty ugly.

Thanks anyway.

Shaun

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

There is no way to modify a trigger or index to change the table it is associated with, you have to drop the old ones and create new ones on the new table.

An easy way to create this script is to use Define Table in Enterprise Manager. Make a change (but don't save it) that requires rebuilding the table, such as inserting a new field at the top. View the change script and either save it or cut and paste. This script should have all the necessary DROP and CREATE commands for indexes, keys, foreign keys, triggers, and extended properties. You just have to remove the dummy field you added, and modify the table rename & data copy sections to suit yourself.

If you need a tool to do this dynamically because someone else may have modified the triggers or indexes, the best approach I can think of would be using SQLDMO to generate all the trigger & index creation commands. Extra work would be required to use this in a stored procedure, maybe put it in a script task in a DTS package and invoke the package from a stored pocedure.

With some effort you could get the latest trigger definition from the text field of the syscomments table (probably requiring concatenating several records for a large trigger), and you could dynamically build the CREATE INDEX statements from information in the system tables. This would not be easy, and you have to worry about system table changes in future SQL Server versions.