create or rename dynamic table

Database

create or rename dynamic table

I want to create or rename table with current_date, f.e

1) create table TABLENAME || CURRENT_DATE 

2) rename TABLE_NAME TO TABLE_NAME_||CURRENT_DATE.

how can I do it?

7 REPLIES
Teradata Employee

Re: create or rename dynamic table

To use dynamic SQL within the database, you must use a stored procedure. Or you could dynamically create a DDL statement on the client before submitting to the database.

Re: create or rename dynamic table

Coule You write an example?

 

I wrote:

CALL DBC.SysExecSQL(

'CREATE TABLE DM_D0_TMP.ru00_all_'||CURRENT_DATE as

    (

        select * from DM_D0_TMP.ru00_all

        sample 100

    ) with data;

)

 and it doesn't work :(

Junior Contributor

Re: create or rename dynamic table

Junior Contributor

Re: create or rename dynamic table

This will append the current date in YYYYMMDD format to the table name. If YYYYMMDD is already appended to the name it's replaced with the new date.

REPLACE PROCEDURE rename_table_yyyymmdd
(
  IN db_name VARCHAR(128) CHARACTER SET Unicode,
  IN tbl_name VARCHAR(128) CHARACTER SET Unicode, -- defaults to current database
  OUT msg VARCHAR(600) CHARACTER SET Unicode
) SQL SECURITY INVOKER
BEGIN
   DECLARE old_name VARCHAR(261)  CHARACTER SET Unicode; 
   DECLARE new_name VARCHAR(261)  CHARACTER SET Unicode;

   DECLARE sql_stmt VARCHAR(600)  CHARACTER SET Unicode;

   SET old_name  = '"' || Coalesce(db_name,DATABASE) || '"."' 
                       || Coalesce(tbl_name, '') || '"';

   SET new_name  = '"' || Coalesce(db_name,DATABASE) || '"."' 
                                -- remove an  existing "_YYYYMMDD" at the end of the table name
                       || Coalesce(RegExp_Replace(tbl_name, '_[0-9]{8}$'),'')    
                       || '_' || To_Char(Current_Date, 'yyyymmdd') || '"';

   SET sql_stmt = 'RENAME TABLE ' || old_name || ' AS ' || new_name || ';'; 

   EXECUTE IMMEDIATE sql_stmt;

   SET msg = 'Table ' || old_name || ' renamed to ' || new_name;

END;

CALL rename_table_yyyymmdd('myDB', 'tablename', msg);
CALL rename_table_yyyymmdd(null, 'tablename', msg);

No error handling, simply fails on errors, e.g. when you run it twice a day or the table doesn't exists or the user has no Drop Table right, etc.

Highlighted

Re: create or rename dynamic table

i wrote Your script to my ETL process. Also I changed variable value and it dosen't work.

Maybe I shoudn't change anything in your script. Jus paste it?

 

create PROCEDURE rename_table_yyyymmdd
(
  IN DatabaseName VARCHAR(128) CHARACTER SET Unicode,
  IN TableName VARCHAR(128) CHARACTER SET Unicode, -- defaults to current database
  OUT msg VARCHAR(600) CHARACTER SET Unicode
) SQL SECURITY INVOKER
BEGIN
   DECLARE old_name VARCHAR(261)  CHARACTER SET Unicode;
   DECLARE new_name VARCHAR(261)  CHARACTER SET Unicode;

   DECLARE sql_stmt VARCHAR(600)  CHARACTER SET Unicode;

   SET old_name  = '"' || Coalesce(DatabaseName,DATABASE) || '"."'
                       || Coalesce(TableName, '') || '"';

   SET new_name  = '"' || Coalesce(DatabaseName,DATABASE) || '"."'
                                -- remove an  existing "_YYYYMMDD" at the end of the table name
                       || Coalesce(RegExp_Replace(TableName, '_[0-9]{8}$'),'')    
                       || '_' || To_Char(Current_Date, 'yyyymmdd') || '"';

   SET sql_stmt = 'RENAME TABLE ' || old_name || ' AS ' || new_name || ';';

   EXECUTE IMMEDIATE sql_stmt;

   SET msg = 'Table ' || old_name || ' renamed to ' || new_name;

END;

CALL rename_table_yyyymmdd('ru00_all', 'tablename', msg);
CALL rename_table_yyyymmdd(null, 'tablename', msg);

 

 

rys.PNG

Teradata Employee

Re: create or rename dynamic table

It looks to be a problem with the client you are using to submit the DDL.

The CREATE PROCEDURE through the END; line must be submitted to the database as a single request.

 

Junior Contributor

Re: create or rename dynamic table

You don't have to run this through your ETL process, you submit it once, either interactively in SQL Assistant/Studio or you put the REPLACE in a flat file and run .COMPILE in BTEQ.