1

What setting should I change to make Teradata replace existing tables with CREATE TABLE query?
Currently, if the table exists, an attemps to CREATE it results in error. So I have to DROP the table before CREATing it.

thx

Grigory P
  • 191
  • 1
  • 8
  • 24
  • 3
    You can't as far as i know. To re-create a table you MUST drop it first. – Mathieu VIALES Oct 25 '17 at 11:43
  • Possible duplicate of [If table exists drop table then create it, if it does not exist just create it](https://stackoverflow.com/questions/20155989/if-table-exists-drop-table-then-create-it-if-it-does-not-exist-just-create-it) – Mathieu VIALES Oct 25 '17 at 11:43
  • 3
    @Wndrr: This is a MySQL extension which is not supported in Teradata. But it can be emulated using a Stored Procedure: https://stackoverflow.com/a/39771103/2527905 – dnoeth Oct 25 '17 at 11:57
  • Of course you might simply ignore the error. – dnoeth Oct 25 '17 at 12:04

1 Answers1

1
REPLACE PROCEDURE DROP_IF_EXISTS(IN table_name VARCHAR(60),IN db_name VARCHAR(60))
BEGIN
  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;

And in your DDL script:

call drop_if_exists('$your_table_name','$your_db_name')
;
database $your_db_name;
create table $your_table_name ...
;
access_granted
  • 1,807
  • 20
  • 25
  • You must add `AND TableKind = 'T'` and better use `dbc.TablesV` (plus VARCHAR(128)). But it's more efficient to simply drop the table and ignore the *table doesn't exist* error, see https://stackoverflow.com/a/39771103/2527905 – dnoeth Oct 27 '17 at 06:08