Delete or Drop a table if it already exists

Database

Delete or Drop a table if it already exists

Hi,

In order fro my code to run, I want to ensure my tables are clear before the query runs again & adds extra data.

So I want to add some code like "If Table_x' Exists, Then Drop Table_x, otherwise continue running the code.

What's available in SQL Assistant 13.11?

Thanks  

Tags (1)
25 REPLIES

Re: Delete or Drop a table if it already exists

SQL Assistant only supports SQL statements and IF-ELSE are not SQL commands. I don't think you can do this in SQLA, BTEQ scripts are the options where you can implement this functionality!

WAQ
N/A

Re: Delete or Drop a table if it already exists

In order fro my code to run, I want to ensure my tables are clear before the query runs again & adds extra data

Why don't you just DELETE the existing data then?

N/A

Re: Delete or Drop a table if it already exists

Since 13.10 SQLA supports some basic conditional logic using BTEQ syntax:

http://developer.teradata.com/note/828

select 1 from dbc.TablesV where databasename = database and TabkeName = 'table_x';

.if activitycount = 0 then GoTo ok

drop table table_x;

.label ok

create table table_x(....)

Dieter

Re: Delete or Drop a table if it already exists

Hi Waq,

I want to avoid having the code stop about 20 times to tell me that the table already exists...

Re: Delete or Drop a table if it already exists

Thanks Dieter,

I have added my database & table as per below;

select 1 from C917348.ACCT_BASE where database = C917348 and table = 'ACCT_BASE'

.if activitycount = 0 then GoTo ok

DROP TABLE ACCT_BASE

.label ok

This is a Volatile table so the database is my id C917348 & the table I want to delete is ACCT_BASE'

I'm getting the error "Expected something between 'and' & 'table' ??

N/A

Re: Delete or Drop a table if it already exists

The Select queries the system tables if the ACCT_BASE table exists

select 1 from dbc.TablesV where databasename = 'C917348' and TableName = 'ACCT_BASE';
.if activitycount = 0 then GoTo ok
drop table ACCT_BASE;
.label ok
create table ACCT_BASE(....)

But this approach will not work when it's a Volatile Table, because VTs are not recorded in the data dictionary.

If all those tables are Volatile the easiest way would be to logoff and logon again, this will drop all VTs.

Dieter

Re: Delete or Drop a table if it already exists

DH,

This sounds like a perfect use for a Global Temp table in your code.  The structure of the table stays in the DD after you log off, eliminating the need to run all that create volatile table repetitively.  Rather than adding complexity to see if its there and make sure that its empty, just run 'Delete * from GlobalTempTable All;' avoid the overhead of the delete * from tbl....

Your structure is there, no need to rebuild the tables, the delete...all; will empty your tables very quickly, and your insert statements can run without worrying about any artifacts...

-Blaine

Re: Delete or Drop a table if it already exists

Thanks but is there a way to delete volatile tables in my script.

This is under testing (as will many other scripts) & I run these over & over in a session so they are still 'There' when I rerun the script.

Having a method of deleting the VTs would be very useful & less time consuming.

Any suggests?

Peter 

Re: Delete or Drop a table if it already exists

I've also tried the script to delete a normal table as below;

I'm getting the error 3706 - "Expected something between 'and' & 'table'.

Where is the code wrong?

I can't think of what should go between 'and' & 'table'?

Thanks

select 1 from DATABASE_NAME.TABLE_NAME

where database = DATABASE_NAME

and table = TABLE_NAME  

.if activitycount = 0 then GoTo ok  

drop table DATABASE_NAME.TABLE_NAME

.label ok