Create temporary tables within SQL Server stored procedures

Hello to sql server 2005 expert,
I tried to preform tmp table, using this SP
CREATE PROCEDURE [dbo].[SPSchd_Create_Temp_MachCalender]
as
begin
  if exists(select * from WizPLan where name like '#Tmp_TMachCalender%')
  drop table #Tmp_TMachCalender
  Select * into #Tmp_TMachCalender from TMachCalender
  select * from #Tmp_TMachCalender
end
go
with out any response. (no error on compilation)
pls advise?
Thank you,
Ygnd
ygndAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuyBDCommented:
try this
CREATE PROCEDURE [dbo].[SPSchd_Create_Temp_MachCalender]
as
begin
  if exists(select * from WizPLan where name like '#Tmp_TMachCalender%')
  begin
    drop table #Tmp_TMachCalender
    Select * into #Tmp_TMachCalender from TMachCalender
    select * from #Tmp_TMachCalender
  end
end
go

Open in new window

0
Louis01Commented:
Hi Ygnd

I am not sure what the WizPLan table is. If you are trying to see if it already exists in order to re-create it, try using this instead:
  if exists(select * from tempdb.sys.tables where name like '#Tmp_TMachCalender%')
      drop table #Tmp_TMachCalender

Perhaps the problem has to do with the scope of your temporary table though? This part about the scope of temporary tables from SQL Books online:

Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when they are no longer used.

There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.

For example, if you create the table employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If a database session creates the local temporary table #employees, only the session can work with the table, and it is deleted when the session disconnects. If you create the global temporary table ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.


0
ygndAuthor Commented:
Thank you for the detailed answer,
Now it's more clear &  it work.
I'm interesting of a temp table that is resident in the "RAM" memory in order to accelerate calculations.
It is the way to do it?

0
Louis01Commented:
I am not an expert at query execution, but I think you might get better results from an indexed view. I might be wrong though... Read the following artcles though. They should guide you through the process to optimize your queries and tell you what to consider in query optimization.

http://technet.microsoft.com/en-us/magazine/cc137757.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3661.mspx?mfr=true
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.