• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1395
  • Last Modified:

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
0
ygnd
Asked:
ygnd
  • 2
1 Solution
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now