Teradata Dynamic SQL Statements

Pradeep

Teradata Dynamic SQL Statements

Sometimes it is required to create SQL statements (DDLs, DMLs etc) dynamically as per some logic/data that is not known earlier.
These dynamic SQL statements are created using Teradata variables.
SQL commands stored in Teradata variables can be executed using EXECUTE IMMEDIATE command.

This tutorial will only cover how to create table on the go and how to generate insert statement dynamically and execute them. Dynamic SQLs statement can also be used to create/replace views, update/delete statment and other normal SQL statements.

Dynamic Table Insertion
Dynamic Inserts

Dynamic Table Creation: Procedure has been created to explain how to create teradata tables on the run.

Data from below table will be used to create columns of new table. It will be used to create as many columns as the rows return by cursor query of this table.


CREATE MULTISET TABLE tutorial_db.column_detail 
( 
  column_id INTEGER,
  column_name VARCHAR(50),
  column_logic VARCHAR(1000)
)
PRIMARY INDEX(column_id);

--Data
INSERT INTO tutorial_db.column_detail VALUES (1, 'col1','LEFT(''Teradata Corportation'',8)');
INSERT INTO tutorial_db.column_detail VALUES (2, 'col2','RIGHT(''Teradata Corportation'',12)');
INSERT INTO tutorial_db.column_detail VALUES (3, 'col3','RANDOM(100,10340)');

Example Procedure: Comments has been added for each step in the procedure to provide explanation of that particular step.


REPLACE PROCEDURE tutorial_user.sp_dynamic_table_creation( OUT proc_msg VARCHAR(5000) )
SQL SECURITY INVOKER
BEGIN
    DECLARE lv_table_first_part_txt     VARCHAR(20000);
    DECLARE lv_table_last_part_txt      VARCHAR(20000);
    DECLARE lv_table_columns_txt        VARCHAR(20000);
    DECLARE lv_final_table_ddl_txt      VARCHAR(60000);
    DECLARE lv_drop_table_ddl_txt      VARCHAR(1000);

    DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' 
    BEGIN
    END;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN          
       SET proc_msg = 'Procedure Failed with error'||SQLCODE;
    /* Any Error handling code if required */
    END;
    
    /* Initialising the column variable with black string*/
    SET lv_table_columns_txt ='';
    
    L0: 
    FOR create_table_cursor AS select_list
    /* Get list of columns from which table will be created*/
    CURSOR FOR    
        SELECT column_id,
               column_name
        FROM   tutorial_db.column_detail
        ORDER BY column_id

    DO  
       /*Creating list of columns with fixed data type in cursor body*/
       SET lv_table_columns_txt = lv_table_columns_txt||' '||create_table_cursor.column_name|| ' VARCHAR(1000),';
    END FOR L0;

    /* Drop existing table*/
    SET lv_drop_table_ddl_txt = 'DROP TABLE tutorial_db.dynamic_table_example;';
    EXECUTE IMMEDIATE lv_drop_table_ddl_txt;


    SET lv_table_first_part_txt =  'CREATE MULTISET TABLE tutorial_db.dynamic_table_example(';

    /*Primary Index can also be specified if there is a fixed know column*/
    SET lv_table_last_part_txt  =  ');';

    /*Removing extra comma from last column, LEFT function(introduced in TD 15) is used to get substring*/
    SET lv_table_columns_txt = LEFT(lv_table_columns_txt, CHAR_LENGTH(lv_table_columns_txt)-1);
    
    /*Complete table body by merging create table and columns*/
    SET lv_final_table_ddl_txt  = lv_table_first_part_txt||lv_table_columns_txt||lv_table_last_part_txt;
    
    /*Table tutorial_db.dynamic_table_example is being created*/
    EXECUTE IMMEDIATE lv_final_table_ddl_txt;

    SET proc_msg = 'Procedure completed successfully';
END;

Note: As table is being recreated each time procedure will be executed therefore existing table need to be dropped.
CONTINUE HANDLER is used to instruct to continue procedure if for some reason table does not exists while executing drop statement (for example: procedure restart scenario).

Running Procedure: Visit this page to know how to compile procedure in teradata.


call tutorial_user.sp_dynamic_table_creation(A);

Output
proc_msg
--------------------------------
Procedure completed successfully


--Newly created table
SHOW TABLE tutorial_db.dynamic_table_example;

CREATE MULTISET TABLE tutorial_db.dynamic_table_example ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      col1 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      col2 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      col3 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( col1 );

Dynamic Inserts: Procedure has been created to explain how to generate insert statements on the go and execute them.

Data from below table will be used to create cursor query and cursor will be used to create INSERT statements on the run.


CREATE MULTISET TABLE test_1 ( col1 INTEGER, col2 INTEGER);

--Data
INSERT INTO test_1 VALUES(1,2);
INSERT INTO test_1 VALUES(2,3);
INSERT INTO test_1 VALUES(3,4);
INSERT INTO test_1 VALUES(4,5);


CREATE MULTISET TABLE test_2( col1 INTEGER, col2 INTEGER);

Comments has been added for each step in the procedure to provide explanation of that particular step.


REPLACE PROCEDURE tutorial_user.sp_dynamic_insert( OUT proc_msg VARCHAR(5000) )
SQL SECURITY INVOKER
BEGIN
    DECLARE lv_insert_txt      VARCHAR(20000);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN          
       SET proc_msg = 'Procedure Failed with'||SQLCODE;
    /* Any Error handling code if required */
    END;

    L0: 
    FOR insert_cursor AS select_list
    /* Getting the list of rows from other table */
    CURSOR FOR    
        SELECT col1,
               col2
        FROM   tutorial_db.test_1

    DO  
    
    /* Generating INSERT statement and storing in variable */
       SET lv_insert_txt = 'INSERT INTO tutorial_db.test_2(col1,col2) VALUES('||TRIM(insert_cursor.col2)||','||TRIM(insert_cursor.col1)||')';
       
       
    /* Executing INSERT statement from variable */
       EXECUTE IMMEDIATE lv_insert_txt;
    END FOR L0;
    
    
    /* Dynamic inserts can be created without cursor as well */
    SET lv_insert_txt = 'INSERT INTO tutorial_db.test_2(col1,col2) VALUES (50,20)';


    /* Executing INSERT statement from variable */
    EXECUTE IMMEDIATE lv_insert_txt;

    SET proc_msg = 'Procedure completed successfully';
END;

Note: Although what has been done in above procedure by creating dynamic inserts, can be done directly without using dynamic variable. But this has been done to share idea on how dynamic DMLs can be prepared and executed

Running Procedure: Visit this page to know how to compile procedure in teradata.


call tutorial_user.sp_dynamic_insert(A);

Output
proc_msg
--------------------------------
Procedure completed successfully


--Newly Populate data
SELECT * FROM test_2;

       col1         col2
-----------  -----------
          5            4
         50           20
          3            2
          4            3
          2            1