Check for table existence when first running sql script runnnig (Advantage data architect)
Good day.
I have the following question:
Is it possible to check the existence of a table the first time a sql script is run?
I am using Advantage Data Architect 11.10.
I want to clarify my question.
In my script, I need to create a temporary table every time I run the sql script. To do this, I drop my temporary table and recreate the 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:
Temporary table not found.
To fix the error, I forced to create a temporary table "with my hands". Then my code that I showed in "Example (1)" worked without errors.
Thank.
Sorry for my English.
source to share
One solution:
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
source to share
--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
source to share