Drop and table creation inside a transaction on SQL Server, impact on other processes

Question

In SQL Server 2005, is it possible to drop and recreate a table (copy from another database) inside a transaction?

Also, will any process trying to access this table during a transaction fail or wait for the transaction to complete?

Answer 1

Copy the data to another table, then switch the old and new tables. Use sp_rename or see Effective data transfer by switching partitions . A later version is better because it retains all permissions and restrictions in the table, but it requires Enterprise Edition.