CODEWITHSUNDEEP
sql-serverstored-procedurestemp-tables
John Bustos
John Bustos

Reputation: 19544

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....

Upvotes: 5

Views: 14245

Answers (4)

GeekSharp
GeekSharp

Reputation: 113

This seems odd, but it works when I try it

IF(OBJECT_ID('tempdb..#Test') IS NULL) --check if it exists
  BEGIN
    IF(1 = 0)--this will never actually run, but it tricks the parser into allowing the CREATE to run
        DROP TABLE #Test;

    PRINT 'Create table';

    CREATE TABLE #Test
    (
        ID  INT NOT NULL PRIMARY KEY
    );
  END

IF(NOT EXISTS(SELECT 1 FROM #Test))
    INSERT INTO #Test(ID)
    VALUES(1);

SELECT *
FROM #Test;

--Try dropping the table and test again
--DROP TABLE #Test;

Upvotes: 0

manderson
manderson

Reputation: 891

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.

Upvotes: 2

S3S
S3S

Reputation: 25112

You meant to use IS NOT NULL i think... this is commonly used to clear temp tables so you don't get the error you mentioned in your OP.

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

The big difference is the DROP TABLE statement after you do your logical check. Also, creating your table without filling data doesn't make it NULL

DROP TABLE #MyTable

CREATE TABLE #MyTable
(
    Col1 INT,
    Col2 VARCHAR(10)
);

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL 
SELECT 1

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Try wrapping your actions in a begin...end block:

if object_id('tempdb..#MyTable') is null
begin
  create table #MyTable (
     Col1 int
   , Col2 varchar(10)
  );
end

Upvotes: 0

Related Questions