Using temp tables with the same name in a stored procedure

temp table in stored procedure
how to use temp table in stored procedure sql server 2012
sql server drop temp table in stored procedure
sql server stored procedure create temp table
how to retrieve data from temp table in sql server
create 10 temp tables using adventureworks database
views vs temp tables
create temp table using select into in sql server

If I have a very basic stored procedure like this in SQL Server 2012:

Create Procedure [MyStoredProcedure] (@ input as int)

As


Select 1 as col1, 2 as Col2
Into #mytemp1

Drop Table #mytemp1

Select 3 as col1, 4 as Col2, 5 as Col3
Into #mytemp1

Drop Table #mytemp1

and I try and run it it fails with the error 'There is already an object named '#mytemp1' in the database.' If this wasn't a stored procedure I could use GO after I initially drop the temp table. Is there a way around this? Thanks

Since there is no explicit need to re-use the same temp table name, just use unique names for each temp table.

Well, technically you could do something like the following:

EXEC('
  Select 1 as col1, 2 as Col2
  Into #mytemp1

  something else related to #mytemp1
');

EXEC('
  Select 3 as col1, 4 as Col2, 5 as Col3
  Into #mytemp1

  something else related to #mytemp1
');

That would not fail as each temp table is isolated in a subprocess that is not parsed until the EXEC actually runs. And, the temp table disappears when the EXEC is finished (hence no need for the explicit DROP statements). But in most cases this is not a practical solution since the typical purpose of creating a temp table is to carry that data along to other operations, but here those temp tables are only viable within their particular EXEC context, so a bit limited.

tsql sql multiple temp tables with same name, SQL server always append some random number in the end of a temp table name (behind the scenes), when the concurrent users create temp  Non temp tables can be only created by on user during execution time with the same name. Temp Tables are created per Session, so every user executing the procedure will have its own table. It think thats the killer argument for you if the proc should be executed more than once in parallel.

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

Create Procedure [MyStoredProcedure] (@ input as in)

As
begin

Select 1 as col1, 2 as Col2
Into #mytemp1

Drop Table #mytemp1

Select 3 as col1, 4 as Col2, 5 as Col3
Into #mytemp1 // you cant use same **temp table** name in sp 

Drop Table #mytemp1
end

And this is not because the table has been dropped and can't be re-created; this code never gets executed, the parser actually sees you trying to create the same table twice

Can two sessions create #temp tables with the same name , Inside each proc, I have the same temp table defined with different column Learn to use ISO-11179 rules for the data element names, avoid  Local temp table created with prefix #, for example #table name and these temp tables can be created with the same name in multiple sessions. Each table contain unique number at end of tablename

check for Existence using OBJECT_ID

Create Procedure [MyStoredProcedure] (@ input as in)

As

if OBJECT_ID('tempdb..#mytemp1') is not null
Drop Table #mytemp1

Select 1 as col1, 2 as Col2
Into #mytemp1 from mytable

--    perform your operations here


if OBJECT_ID('tempdb..#mytemp1') is not null
Drop Table #mytemp1

Select 3 as col1, 4 as Col2, 5 as Col3
Into #mytemp1 from mytable

--    perform your operations here

Can the same temp table name in different stored procs ever run into , Can you create two #SameName temp tables in a single session? while the SP is WAITFORing, which isn't even a word (if it's doing that at  “A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it is not defined which table the query is

QuickQuestion: Can you create two #SameName temp tables in a , Stored procedures can reference temporary tables that are created during the current session. create a temporary table, drop it, and then create a new temporary table with the same name. Use create table to create the temporary table. SQL Server adds some postfix to local temporary objects to distinquish the objects with the same name from different sessions. Now, in the same window, execute the local & global temporary stored

Manipulating temporary tables in stored procedures, Different clients can create temporary tables with the same name without causing However, we can create a stored procedure that checks if a temporary table  SQL Server allows users to assign the same name to two different local temp tables – so long as the tables are from different connections. If this happens, then the beginning part of the both local temp tables will have the same name. However, the trailing part of the name will be distinct for each local temp table with the same beginning part.

MySQL Temporary Table, To create temporary tables, we use the exact same command that we use to select name from tempdb..sysobjects where name like '#myTable%' So all the database objects that we create - like stored procedure, tables,  Multiple database connections can create temporary tables with the same name, therefore to differentiate between the temporary tables created by different connections, the database server automatically appends this unique identifier at the end. You can perform operations on the temporary table via the same connection that created it.

Comments
  • (@ input as in) What exactly are you trying to achieve here, other than giving us an example that doesn't show your problem because it's so syntactically invalid there are two errors in the space of 10 characters?
  • Is there a reason why you are using the same name twice instead of a different name for the second temp table?
  • @srutzky I guess the only reason is to save some time in building a stored procedure.
  • Thanks. I did start to do that and I thought I'd end up having to do it but was curious as to whether it could be done.
  • @user3634559 Yeah, if it's a parse error then there is nothing you can do. For some parse errors you can encapsulate the query within an EXEC('...') but that won't work for temp tables since they will disappear as soon as that subprocess is finished.
  • @user3634559 I just updated my answer with an example of something that might work, but not terribly practical. But if it might work for your needs then certainly doesn't hurt to try, right?
  • This will not work - I have tried to use this as a security blanket, and it still threw the same error