How do I conditionally create a table in Sybase (TSQL)?

OK, so Sybase (12.5.4) will let me do the following to DROP a table if it already exists:

IF EXISTS (
    SELECT 1
    FROM sysobjects
    WHERE name = 'a_table'
    AND type = 'U'
)
DROP TABLE a_table
GO

But if I try to do the same with table creation, I always get warned that the table already exists, because it went ahead and tried to create my table and ignored the conditional statement. Just try running the following statement twice, you'll see what I mean:

IF NOT EXISTS (
    SELECT 1
    FROM sysobjects
    WHERE name = 'a_table'
    AND type = 'U'
)
CREATE TABLE a_table (
    col1 int not null,
    col2 int null
)
GO

Running the above produces the following error:

SQL Server Error on (localhost) Error:2714 at Line:7 Message:There is already an object named 'a_table' in the database.

What's the deal with that?!

Answers


The only workaround I've come up with so far is to use execute immediate:

IF NOT EXISTS (
    SELECT 1
    FROM sysobjects
    WHERE name = 'a_table'
    AND type = 'U'
)
EXECUTE("CREATE TABLE a_table (
    col1 int not null,
    col2 int null
)")
GO

works like a charm, feels like a dirty hack.


There is no other way than calling create table in execute("create table ...")

SYBASE Manual says:

When a create table command occurs within an if...else block or a while loop, Adaptive Server creates the schema for the table before determining whether the condition is true. This may lead to errors if the table already exists. To avoid this situation, either make sure a view with the same name does not already exist in the database or use an execute statement, as follows:

if not exists
    (select * from sysobjects where name="my table")
begin
execute "create table mytable(x int)"
end

I haven't tested this, but you could try moving the create table statement into a sproc. You could then conditionally call that sproc based on your existing if statement.


Assign the "CREATE TABLE" statement in a char @variable and then do an EXEC(@variable).


If you want to always create the table, but conditionally drop it, you can use:

IF(SELECT count(*) FROM sysobjects WHERE name="tableNameWithoutUserPart") > 0
    DROP TABLE tableNameWithUserPart
GO

CREATE TABLE tableNameWithUserPart ...

There are no workarounds needed ;)

According to the documentation:

CREATE [ GLOBAL TEMPORARY ] TABLE [ IF NOT EXISTS ] [ owner.]table-name
( { column-definition | table-constraint | pctfree }, ... )
[ { IN | ON } dbspace-name ]
[ ENCRYPTED ]
[ ON COMMIT { DELETE | PRESERVE } ROWS
   | NOT TRANSACTIONAL ]
[ AT location-string ]
[ SHARE BY ALL ]

Just use the IF NOT EXISTS.


IF object_id('a_table') IS NULL
BEGIN
    CREATE TABLE a_table (
        col1 int not null,
        col2 int null
    ) 
END

This works when tested with Sybase anywhere 10.01 :

if not exists(select * from SysColumns where tname = 'AAA') then create table DBA.AAA(  UNIQUEID integer not null ) END IF ;

Try using Begin and End.

IF NOT EXISTS ( SELECT Count(1) FROM sysobjects WHERE name = 'a_table' AND type = 'U' ) BEGIN CREATE TABLE a_table ( col1 int not null, col2 int null ) END GO


Need Your Help

querying mongodb using pymongo

python mongodb python-2.7 pymongo

i just started using mongodb and setup a test database to handle web scraping results from a couple scripts i created. right now, the date_found is being loaded as a string. when i run this in mong...

Catching Exception inside Using statement

c# using

I know that Using statement disposes out the object that is being created. Like if I wanted to do something like this: