Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - Create temp table if doesn't exist

In my SQL Server 2012 environment, I've created a series of stored procedures that pass pre-existing temporary tables among themselves (I have tried different architectures here, but wasn't able to bypass this due to the nature of the requirements / procedures).

What I'm trying to do is to, within a stored procedure check if a temporary table has already been created and, if not, to create it.

My current SQL looks as follows:

IF OBJECT_ID('tempdb..#MyTable') IS NULL
    CREATE TABLE #MyTable
    (
        Col1 INT,
        Col2 VARCHAR(10)
        ...
    );

But when I try and run it when the table already exists, I get the error message

There is already an object named '#MyTable' in the database

So it seems it doesn't simply ignore those lines within the If statement.

Is there a way to accomplish this - create a temp table if it doesn't already exist, otherwise, use the one already in memory?

Thanks!

UPDATE:

For whatever reason, following @RaduGheorghiu's suggestion from the comments, I found out that the system creates a temporary table with a name along the lines of dbo.#MyTable________________________________________________0000000001B1

Is that why I can't find it? Is there any way to change that? This is new to me....

like image 878
John Bustos Avatar asked Oct 17 '22 16:10

John Bustos


1 Answers

Following the link here, http://weblogs.sqlteam.com/mladenp/archive/2008/08/21/SQL-Server-2005-temporary-tables-bug-feature-or-expected-behavior.aspx

It seems as though you need to use the GO statement.

like image 56
manderson Avatar answered Oct 27 '22 22:10

manderson