Temp Table existence check

Question

I tried the following code in SQL Server and met a strange error:

IF OBJECT_ID(N'tempdb..#t1') IS NULL
BEGIN
CREATE TABLE #t1(
    F1 DATE NOT NULL
)
END

IF OBJECT_ID(N'tempdb..#t1') IS NULL
BEGIN
CREATE TABLE #t1(
    F1 DATE NOT NULL
)
END

Error:

'#t1' existed.

I have no idea why this error occurs?

It seems like your IF clauses are being evaluated before your first create table is executed, if you put a GO statement after your first IF...END then you don't receive the error. — Steve Ford, Dec 11 '12 at 09:52

score 2 · Accepted Answer · answered Dec 11 '12 at 09:39

2

This is idea

IF OBJECT_ID(N'tempdb..#t1') IS NULL
BEGIN
   CREATE TABLE #t1( F1 DATE NOT NULL )
END
GO

IF OBJECT_ID(N'tempdb..#t1') IS NULL
BEGIN
   CREATE TABLE #t1( F1 DATE NOT NULL )
END
GO

answered Dec 11 '12 at 09:39

Hamlet Hakobyan

32,965
6
52
68

Thanks! Now I know 'GO' is needed. – xiagao1982 Dec 12 '12 at 01:13

score 1 · Answer 2 · answered Dec 11 '12 at 09:39

1

Try this instead:

IF NOT exists(SELECT 1
              FROM information_schema.columns 
              WHERE table_name LIKE '#t1%')
BEGIN
CREATE TABLE #t1(
    F1 DATE NOT NULL
)
END

answered Dec 11 '12 at 09:39

Mahmoud Gamal

78,257
17
139
164

score 1 · Answer 3 · answered Dec 11 '12 at 09:42

1

Try this one :

IF Not EXISTS (select * from sys.objects where Name = 'tempdb..#t1')

BEGIN
CREATE TABLE #t1(
    F1 DATE NOT NULL
)
END

answered Dec 11 '12 at 09:42

Maryam Arshi

1,974
1
19
33

score 1 · Answer 4 · answered Dec 11 '12 at 09:51

This is not runtime Error, it's a "compiletime" error

--Drop table #t1  // call this alone to ensure that it's not existing, then call the rest, nothing will be printed

Print 0

IF OBJECT_ID('tempdb..#t1') IS NULL
BEGIN
Print 1
CREATE TABLE #t1(
    F1 DATE NOT NULL
)
END



IF OBJECT_ID('tempdb..#t1') IS NULL
BEGIN
Print 2
CREATE TABLE #t1(
    F1 DATE NOT NULL
)
END

score 1 · Answer 5 · answered Dec 11 '12 at 10:01

SQL Server does not store temp table with given name ('#t1'). Instead it adds a suffix to make it 128 character long. Therefore you won't find '#t1'.

You can use LIKE '#t1%' to find it.

IF NOT EXISTS (select * from tempdb.sys.tables where name like '#t1%')

Also you should add GO after your first query to mark the end of batch.

Use it as below;

--DROP TABLE #t1
--GO

    IF OBJECT_ID(N'tempdb..#t1') IS NULL
    BEGIN
    CREATE TABLE #t1(
        F1 DATE NOT NULL
    )
    END
    GO

    IF NOT EXISTS(select * from tempdb.sys.tables where name like '#t1%')
    BEGIN
    CREATE TABLE #t1(
        F1 DATE NOT NULL
    )
    END

score 1 · Answer 6 · edited May 23 '17 at 12:04

1

Your code is actually fine, but there's a limitation in SQL Server which means that during the query optimization phase it will fail. Please see the answers here - why does sql server think a temp table already exists when it doesnt.

edited May 23 '17 at 12:04

Community

1
1

answered Dec 11 '12 at 10:02

Matt Whitfield

6,436
3
29
44

Temp Table existence check

6 Answers6