Replace table with a temp table with zero delay

[dkillilea]

Please help! I need to replace a table with another. Both contain the same columns, one has more current MTD data. Because I need 100% uptime I can not insert b/c it's takes so long, it's 10,000,000 rows

TABLE1 - feeds an Inventory View right now. I need to replace it completely with the next table as soon as that one is imported, prepped and ready to be switched over. I can not insert or replace because of the specific nature of this table. I just need the next table to become this one, with zero delay.

TABLE1_TEMP - just like table1, but imported while everone is using TABLE1 so TABLE1 can constantly support it's inventory view. Again I can not just insert into if different because that takes to long and it will not work due to the specific nature of this table.

Can anyone direct me. I need TABLE1_TEMP to become TABLE1 with zero delay.

Thanks,
Doug

 

[mrdenny]

Within a single transaction rename table1 to table1_old and rename table1_temp to table1.

begin transaction
    exec sp_rename 'table1', 'table1_old', 'object'
    exec sp_rename 'table1_temp', 'table1', 'object'
commit

This will make the change happen within a split second. Any forign key constraints will need to be dropped from the old table and recreated on the new table, as well as any indexes. However this will get the new table into place with no outage.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com