Create temporary tables within SQL Server stored procedures

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

Select allOpen in new window

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.

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?