Rename a temporary table to a physical

Question

Can I do something like this?

create table #tbl_tmp (col1 int) insert into #tbl_tmp select 3 exec sp_rename '#tbl_tmp','tbl_new' 

Answer 1

Not.

If you use this from a database other than tempdb , you get

An item named '#tbl_tmp' cannot be found in the current database ....

Which is not surprising, since all data pages, etc. are in tempdb data files, so you cannot rename it to suddenly become a permanent table in another database.

If you use this from tempdb , you get

An invalid parameter or parameter was specified for the 'Sys.sp_rename' procedure.

If you execute EXEC sp_helptext sp_rename and look at the definition corresponding to a bit of code prohibiting this,

 -------------------------------------------------------------------------- -------------------- PHASE 32: Temporay Table Isssue ------------------- -------------------------------------------------------------------------- -- Disallow renaming object to or from a temp name (starts with #) if (@objtype = 'object' AND (substring(@newname,1,1) = N'#' OR substring(object_name(@objid),1,1) = N'#')) begin COMMIT TRANSACTION raiserror(15600,-1,-1, 'sys.sp_rename') return 1 end 

Why don't you just create a persistent table first and then rename it?

Answer 2

As far as I know, this is not possible outside of tempdb .

Instead of renaming the table, you can create a new temporary one.

Unverified:

 SELECT * INTO tbl_new FROM #tbl_tmp 

Answer 3

#tbl_tmp and tbl_new are two different objects: #tbl_tmp is stored in the tempdb system database, and tbl_new (usually) is stored in the user database.

So:

• Renaming a temp table to a standard table may involve moving this object from the source database to another database.
• Renaming the temp table to a standard table means that you want to convert an object from one type to another type (in both cases type (from the sys.objects view) U , but because in SQL Server behave otherwise I think it's right to assume that these two objects have a different type). This is similar to converting a temp table to a table variable by renaming.

In both cases, I do not think that this operation can be performed using only sp_rename .

Answer 4

Answer: Yes. You can implement something like this, but in a workaround. Try the following approach, a small old school, but get around the limitation. I tested it myself

 /* Create an empty temporary staging table **/ use aw_08r2 go -- create temporary table select * into #temp from person.address -- select data from temporary staging table select * from #temp -- convert the temporary table and save as physical table in tempdb select * into tempdb.dbo.test from #temp -- save a copy of the physical table from tempdb in aw_08r2 select * into person.test from tempdb.dbo.test -- select data from physical table select * from #temp select * from tempdb.dbo.test select * from person.test -- drop temporary table and physical table from tempdb drop table #temp drop table tempdb.dbo.test go