How to create a table only if it does not exist? Using "object_id ('table') IS NULL" doesn't work?

Question

I would like to check if a table exists, and then create it, otherwise insert data into it.

use tempdb if object_id('guest.my_tmpTable') IS NULL begin CREATE TABLE guest.my_tmpTable ( id int, col1 varchar(100) ) end --- do insert here ... 

At the first start, the table was created, but the second start of sybase complains that the table already exists.

Thanks!

Answer 1

The reason is that the entire if statement and its "true" part are compiled as one.

If the table exists at compile time - an error.

So you can put the CREATE TABLE statement in dynamic sql statemetn EXEC('CREATE TABLE....')

Then everything that Sybase sees when compiling:

 IF object_id('mytab') IS NULL EXEC('something or other') 

EXEC content does not compile before execution, when you do not know that there is no table, and all is well.