Assuming this is Sybase ASE ...
The error (index already exists on table) is likely due to the conditional ...
IF NOT EXISTS(SELECT 1 FROM sysindexes where id=OBJECT_ID('#TABLE_X') and name ='IDX')
... being run against
sysindexes in the wrong database.
If I'm sitting in database
userdb and I create a table
#TABLE_X, the above query is going to check for the existence of an index on
#TABLE_X in the current database named
userdb, which will always return 'true' (ie, no index exists for this table in the
userdb database), which in turn means the
create index will be run each time the conditional is run; first time conditional is run =>
create index succeeds; second time conditional is run =>
create index generates the error.
The conditional needs to be run against the
sysindexes table in the temporary database where the
#TABLE_X table resides.
For older versions of ASE, or ASE instances with a single temporary database, this means running the conditional against
tempdb..sysindexes however ...
In ASE's with multiple temporary databases it's first necessary to first figure out which temporary database the
#TABLE_X table resides in and then query the associated
While this outlines the appropriate method of determining the existence of the index, there's the bigger question of why check for the existence of the index after each insert? Ideally the
create index should be tested for (and created) just once ... either before or after the
insert (depending on overall code design and when in the process the index is to be used).
I would probably pull the
create index out to a different place in your code, eg:
create index, looping/insert,
update statistics <table>, run queries
create/select-into <table>, looping/insert,
create index, (optionally
update statistics <table> - depends on your query requirements), run queries
If you insist on throwing the
create index into the middle of your looping/insert construct (I wouldn't recommend this), consider ...
- to query the system tables you need to know which tempdb your session is assigned to (assuming your ASE is configured with multiple tempdb's), and then query the system tables in that particular tempdb (eg, build a dynamic query)
- you can use some of the built-in system functions to run some tests
Your current tempdb's database id can be found in
@@tempdbid (or you can call
tempdb_id()). You can then call
db_name(tempdb_id())) to get your tempdb name. From here you can build your dynamic query against sysindexes. [Plenty of posts on dynamic ASE queries so I'm not going to cover that here.]
Instead of building a dynamic query I'd probably opt for a (relatively) simple function-based test, eg:
NOTE: Assuming no other indexes on the table ... the index id should be 1 or 2 ... depending on the table's locking scheme and the type of index (clustered/non-clustered).
if index_name(@@tempdbid,object_id(#TABLE_X),1) is NULL
and index_name(@@tempdbid,object_id(#TABLE_X),2) is NULL
create index ...
Regardless of whether you build/run a dynamic query, or use the system functions to test for the existence of the index, I would highly recommend you re-think your code design. Do you really, Really, REALLY want to add the extra overhead for a dynamic-query/function-call to every pass through your looping/insert construct?
fetchcommand inside the loop; do you really want to a) pull just a single row from the cursor and b) get into an infinite loop