validate if table exists while selecting and inserting SQL Server

Question

Hello I have a dynamic query like

SET  @template = 'SELECT x AS X,... INTO temporalTable FROM' + @table_name

Then I execute it

EXEC (@template)

How do I validate if temporalTable already exists, if so, drop it?

score 2 · Answer 1 · answered Feb 18 '11 at 06:42

2

Just use OBJECT_ID

IF OBJECT_ID('temporalTable') IS NOT NULL
   DROP TABLE temporalTable

No need to query any tables or do any aggregations.

answered Feb 18 '11 at 06:42

gbn

422,506
82
585
676

+1 Clearly a better option than my answer. But counting on the information schema should not be that bad either. After all, object dictionary is probably in memory all the time. – Pablo Santa Cruz Feb 18 '11 at 11:08

score 1 · Accepted Answer · answered Feb 18 '11 at 02:53

1

Use information schema or sp_help function.

I would prefer information schema since it's SQL ANSI and you can port the code to other databases:

select count(1)
  from information_schema.tables 
 where table_name = 'temporalTable';

sys.tables is a SQLServer specific option similar to inforamtion schema that you can also explore.

answered Feb 18 '11 at 02:53

Pablo Santa Cruz

176,835
32
241
292

Overkill: a query with an aggregate. COUNT(1) is superstition too http://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649 – gbn Feb 18 '11 at 06:44
It's not wrong of course. It just offends the OCD part of me :-) – gbn Feb 18 '11 at 11:21

score 1 · Answer 3 · answered Feb 18 '11 at 02:55

1

IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_NAME='tablename') 
        SELECT 'tablename exists.' 
ELSE 
        SELECT 'tablename does not exist.'

answered Feb 18 '11 at 02:55

Paul Rowland

8,244
12
55
76

validate if table exists while selecting and inserting SQL Server

3 Answers3