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, b

Solution 1:

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.

Solution 2:

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

Solution 3:

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

Solution 4:

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;