[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1388
  • 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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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