If you're like me, there are times when you're running SQL that you neeed to drop a table and re-create it as a job runs through. Problem is, if the table doesn't exist when you try to
DROP it, your SQL will exit with an error. The way to get around this is to test if the table exists first, and then only issue the
DROP command if it's needed.
Checking for a normal table (non-volatile)
This code snippet will do this for you :-)
SELECT 1 FROM dbc.TablesV WHERE databasename = 'MAIN_DB' AND TABLENAME = 'Your_Table_Name'; .IF ACTIVITYCOUNT = 0 THEN GOTO ok DROP TABLE MAIN_DB.Your_Table_Name; .LABEL ok CREATE TABLE Your_Table_Name AS ( ...
Note: If it's appropriate, for a non-volatile table, consider clearing the contents of a table rather than doing a
CREATE..., your DBAs will bless you as it's less load intestive on the database. You can do this using the following command:
DELETE FROM MAIN_DB.Your_Table_Name;
Checking for a volatile table
However, the method outlined above, won't work for volatile tables because they're not recorded in the data dictionary.
Which is a shame because a lot of my jobs use volatile tables, and if you're anything like me, you'll re-run jobs a lot as you're developing them. Of course, you can clear volatile tables by logging out and back in again of course, but that's kinda annoying.
But never fear, the following SP will work for volatile tables :-)
-- Drop the volatile table if it exists REPLACE PROCEDURE MAIN_DB.drop_voltable(IN pVolTable VARCHAR(32)) BEGIN DECLARE sqlstr VARCHAR(500); DECLARE exit HANDLER FOR SQLSTATE '42000' BEGIN end; SET sqlstr = 'drop table ' || pVolTable; EXECUTE IMMEDIATE sqlstr; end; CALL MAIN_DB.drop_voltable('Your_Table_Name'); CREATE VOLATILE TABLE Your_Table_Name AS ( ...
Note: I would love to credit for both of these solutions, but in truth I found them while googling to solve the problem. Sources and credits are given appropaitely.
Main Source: Thread on "Delete or Drop a table if it already exists" in the Teradata Community
Credit for normal table code: Dieter(dnoeth)/Teradata Community
Credit for volatile table SP: Thorkil/Teradata Community
"It is a capital mistake to theorize before one has data." - Arthur Conan Doyle