All Forums UDA
Portuga 4 posts Joined 02/07
07 Feb 2007
Drop Table "if exists"

Hi,I can use the Drop Table "if exists" command in my SQL.but when I use this command in Teradata I get an error message.Is there any way I can drop a table only if it already exists in Teradata SQL Assistant 7.1?Thanks

07 Feb 2007

Hi,You cannot do this from Queryman, no matter what version you are using. The only option is to use BTEQ for this operation. You can use something like below:SELECT 1FROM DBC.TABLESWHERE Databasename = ''AND Tablename (= '' or like '%%');.if activitycount = 0 then .goto no_tab;.if errorcode <> 0 then .goto unsuccessful_end;DROP TABLE $dwh.table_name;.if errorcode <> 0 then .goto unsuccessful_end;.label no_tab;-----.label unsuccessful_end;exit 12;In this case, if the first query returns any row, it means the table exists and hence activitycount <> 0, the the control continues below and drops the existing table. If the table does not exist, then the first query does not return any data and hence activitycount = 0 wherein the control is transferred to the part with label no_tab which has already skipped the section with the DROP and hence DROP statement will not execute. Hope that helps.

jeuell 1 post Joined 07/10
02 Sep 2010

Has anyone been able to create a stored proc or function that accomplishes the same thing and can be run from sql Assistant?

I need to be able to drop a bunch of tables or procedures that may or may not exist so that I can create brand new tables and procedures with the same names without stalling the procedure.

Adeel Chaudhry 773 posts Joined 04/08
03 Sep 2010

The solution mentioned above can be easily implemented in Teradata and obviously can be called from SQL Assistant.

Regards, MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

rluebke 65 posts Joined 11/05
07 Sep 2010

In BTEQ, the easiest way to do this is to set your ERRORLEVEL severity

.SET ERRORLEVEL 3807 SEVERITY 0

(3807 is the error thrown when an object does not exist)
Now, when you try to drop a table that already exists, it will ignore the error and keep on processing.

R

jc_ua 1 post Joined 01/10
08 Sep 2010

We used next Teradata procedure in our project.

CREATE PROCEDURE PROD_TEST.DROP_TABLE
(
IN a_tablename VARCHAR(60),
IN a_databasename VARCHAR(60) /*NULL allowed. PROD_TEST used by default*/
)
BEGIN
DECLARE db_name, table_name VARCHAR(60);

SET db_name = TRIM(COALESCE(a_databasename,'PROD_TEST'));
SEt table_name = TRIM(a_tablename);

IF EXISTS(SELECT 1 FROM dbc.tables WHERE databasename = db_name AND tablename = table_name) THEN
CALL DBC.SysExecSQL('DROP TABLE ' || db_name ||'.'|| table_name);
END IF;
END;

You must sign in to leave a comment.