Renaming a temporary table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Renaming a temporary table

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-08 : 13:07:10

I am doing the following

create table #distinct_con_num ( con_num varchar(50) )
create table #del_status ( con_num varchar(50), cust_ref varchar(50), job_num varchar(50), dest_dep varchar(50), con_date varchar(50), summ_desc varchar(50))

do some stuff and then I want to drop the existing table "summary" and rename the #del_status to summary

Is it best to drop the table first as
EXEC sp_rename '#del_status', 'summary'
doesn't seem to work as I get the following error : -

Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'del_status' could be found in the current database 'onlinetrack', given that @itemtype was input as '(null)'.

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-08 : 13:12:24

To explain myself more I would like the temporary table to replace the exisiting permanent table and thus the temporary table would become permanent

Is this possible?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-08 : 13:15:25

Try a global temp table

EXEC sp_rename '##del_status', 'summary'

Andy

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-08 : 13:16:20

tried that still get the same error message

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-08 : 13:26:43

i think your problem lies in that #del_status is in tempDb database and not in 'onlinetrack' database.

select *
into onlinetrack.dbo.NewTableName
from #del_status

drop table #del_status

Go with the flow & have fun! Else fight the flow

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-08 : 13:33:08

Cheers spirit1 I did the following

drop table summary
select * into onlinetrack.dbo.summary from #del_status

drop table #del_status
drop table #distinct_con_num

the only problem I have now is that I can't do the select on it as I now get : -

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'summary', database 'onlinetrack', owner 'dbo'.

How do I set the select permission for a particular user when i create the new table?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-08 : 13:35:04

chanege dbo to your username.

Go with the flow & have fun! Else fight the flow

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-08 : 13:36:45

can you give an example please not so good on that stuff :-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-08 : 13:37:33

oh yeah sine you do
drop table summary
maybe you should simply do
tuncate table summary
and insert into it...
you remove the hassle of creating it again.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-08 : 13:42:33

quote:
Originally posted by spirit1

oh yeah sine you do
drop table summary
maybe you should simply do
tuncate table summary
and insert into it...
you remove the hassle of creating it again.

Go with the flow & have fun! Else fight the flow