renaming a temporary table into a physical one

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

No.

If you are running this from a database other than tempdb you get

No item by the name of '#tbl_tmp' could be found in the current database ....

Which is not surprising as all the data pages etc. are in the tempdb data files so you wouldn't be able to rename this to suddenly become a permanent table in an other database.

If you are running this from tempdb you get

An invalid parameter or option was specified for procedure 'sys.sp_rename'.

If you do EXEC sp_helptext sp_rename and look at the definition the relevant bit of code disallowing this is

--------------------------------------------------------------------------  
 --------------------  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 wouldn't you just create a permanent table in the first place then do the rename?

Answer 2

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

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

Untested:

SELECT * 
INTO tbl_new
FROM #tbl_tmp