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

Question

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?!

What's with downvoting answers? And then award yourself the acceptance? For a non-answer? — dkretz, Feb 01 '09 at 03:21
I've downvoted answers that don't help in answering the question, that's the purpose of downvoting. I've upvoted answers that helped and were viable, but in the end the only solution that worked for me was my own. I don't get rep for answering my own question so it's not to my advantage to do so. — ninesided, Feb 01 '09 at 10:24
Fair enough. I'll remember to not respond unless I'm certain your question is dealt with by my answer. In this case, there wasn't enough information to know, so I was wasting my time. — dkretz, Feb 01 '09 at 22:57
OK, I can see where you're coming from, you invested time in trying to solve my question, which I should thank you for, not penalize you. Whilst the other downvoted answers were simply incorrect, yours was more considered - I admit I was too hasty in downvoting it. Apologies, I have revoked the -1. — ninesided, Feb 02 '09 at 00:26

score 20 · Accepted Answer · answered Nov 21 '08 at 05:49

20

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.

answered Nov 21 '08 at 05:49

ninesided

23,085
14
83
107

4

I know you answered this a long time ago, but for other peoples information this is exactly how the sybase manual states a conditional create needs to happen because "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..." – scrappedcola Apr 20 '11 at 20:13
1

Agreed.. Just wanted to include a link to the docs http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands61.htm – geaw35 Aug 14 '12 at 17:42
FYI, You may have to put the execute command in single quotes rather than double quotes. – Malcolm May 15 '18 at 13:14

score 7 · Answer 2 · edited May 11 '15 at 17:21

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

score 2 · Answer 3 · answered Nov 21 '08 at 17:18

2

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.

answered Nov 21 '08 at 17:18

AdamH

1,331
7
19

Mark Rhodes · Answer 4 · 2012-08-29T14:15:47.157

1

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 ...

edited Aug 29 '12 at 14:15

answered Feb 21 '11 at 15:05

Mark Rhodes

10,049
4
48
51

score 1 · Answer 5 · answered Jun 12 '09 at 17:23

1

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

answered Jun 12 '09 at 17:23

B0rG

1,215
12
13

Diego Frehner · Answer 6 · 2012-12-22T10:52:35.420

0

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.

edited Dec 22 '12 at 10:52

answered Nov 02 '12 at 10:21

Diego Frehner

2,396
1
28
35

1

This is wrong, since OP is looking at Sybase ASE, whose documentation is here: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01840.1600/doc/html/san1281564827401.html. The grammar you copy is from SAP Sybase IQ http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01840.1600/doc/html/san1281564827401.html which is another product. – nhahtdh Mar 10 '15 at 10:39
The syntax i copied is from my offline Sybase Anywhere 12 Documentation and that syntax works there. Ok didnt see the ASE tag ;) – Diego Frehner Mar 10 '15 at 12:50

score -1 · Answer 7 · edited Jan 16 '15 at 15:51

-1

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 ;

edited Jan 16 '15 at 15:51

Bence Kaulics

7,066
7
33
63

answered Jan 16 '15 at 15:36

Peter Cichoszewski

1

1

this question relates to ASE, not Anywhere – ninesided Jan 16 '15 at 15:46

score -1 · Answer 8 · answered Nov 21 '08 at 05:24

-1

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

answered Nov 21 '08 at 05:24

Eugene Yokota

94,654
45
215
319

SquidScareMe · Answer 9 · 2008-12-01T17:33:03.920

-2

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

edited Dec 01 '08 at 17:33

answered Dec 01 '08 at 17:20

SquidScareMe

3,108
2
24
37

sorry, no dice. "There is already an object named 'a_table' in the database". – ninesided Dec 11 '08 at 02:02

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

9 Answers9

Linked