renaming a temporary table into a physical one

Can I do something like this?

create table #tbl_tmp (col1 int)

insert into #tbl_tmp select 3

exec sp_rename '#tbl_tmp','tbl_n开发者_运维百科ew'

---

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?

---

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

---

The answer is Yes. You can implement something like it but in a workaround way. Try the following approach, a lil bit old school but bypasses the restriction. I tested it myself as well

/* 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