[ACCEPTED]-How do I conditionally create a table in Sybase (TSQL)?-sap-ase

Accepted answer
Score: 19

The only workaround I've come up with so 2 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 1 a charm, feels like a dirty hack.

Score: 7

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

SYBASE 10 Manual says:

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

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

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

Score: 1

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

Score: 1

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

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

CREATE TABLE tableNameWithUserPart ...
Score: 0

There are no workarounds needed ;)

According 2 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 1 EXISTS.

More Related questions