Check the existence of the table when sql script runnnig first time (Advantage data architect)

Question

Good day.

I have the following question:

Is it possible to check the existence of the table when sql script running first time?

I'm using Advantage Data Architect 11.10.

I want to clarify my question.

In my script I need to create a temporary table each time when sql script starting. For do this I delete my temporary table and recreate table. For example (1):

...
if exists (select * from #tmp) then
delete table #tmp;
end if;

create table #tmp (g integer);
...

But when I run my script for the first time I get the following error:

The temporary table cannot be found.

To fix the error, I forced to create a temporary table by "my hands". Then my code which I showed in "For example (1)" worked without errors.

Thanks.

Sorry for my English.

Answer 1

One solution is this:

TRY DROP TABLE #tmp; CATCH ALL END TRY;

CREATE TABLE #tmp ...

Another solution:

IF NOT EXISTS (SELECT 1 FROM (EXECUTE PROCEDURE sp_GetTables (NULL, NULL, 'tmp', 'LOCAL TEMPORARY')) getTables ) THEN
  CREATE TABLE #tmp ...
END IF;

See also here:

http://devzone.advantagedatabase.com/forum/questions/5573/determine-if-temp-table-exists

Answer 2

You could use

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp

See this question.

Answer 3

--first variant

--if table exist then drop table
IF OBJECT_ID('Tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp
CREATE TABLE #tmp
    (
      ID INT ,
      Definitions VARCHAR(30)
    )
-------------------------------------------------------------------------
--second variant
--if table exist then delete all records from table, otherwise create table
IF OBJECT_ID('Tempdb..#tmp') IS NOT NULL
    TRUNCATE TABLE #tmp --delete all records from temp table
ELSE
    BEGIN   
        CREATE TABLE #tmp
            (
              ID INT ,
              Definitions VARCHAR(30)
            )
    END