[ACCEPTED]-How do I conditionally create a table in Sybase (TSQL)?-sap-ase
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.
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
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.
Assign the "CREATE TABLE" statement in a 1 char @variable and then do an EXEC(@variable).
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 ...
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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.