Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exists

Database
Enthusiast

Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exists

I am trying to write a procedure that will conditionally execute either 1) a CREATE TABLE xxxxx AS () SELECT statement if the table does not exist or 2) execute a INSERT INTO statement if the table already exists. I am having problems somewhere with the syntax as it keeps returning an error of " EXPLAIN Failed. 3706:  Syntax error: Invalid  SQL Statement."  Admittedly I don't have a lot of experience writing procedures yet, so any advice would be appreciated.

 

I think one issue is getting the string parameters to pass correctly to the SELECT statement within the EXISTS() check on the IF because of the single quotes and getting it to escape right. Otherwise I can't see any glaring syntax errors that would prevent it from executing. I have ran EXPLAINs and executed both the CREATE TABLE and INSERT INTO statements independently outside of the CREATE PROCEDURE statement and they run fine.

 

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 7388 StartFragment: 314 EndFragment: 7356 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

--EXPLAIN
CREATE PROCEDURE mydb.table_exists_proc (IN DB_name VARCHAR(20), IN TBL_name VARCHAR(50))BEGIN
IF EXISTS(SELECT 1 FROM dbc.tables WHERE DatabaseName =''''||:DB_name||'''' AND TABLENAME = ''''||:TBL_name||'''') THEN
        CALL DBC.SysExecSQL('
            INSERT INTO '||:DB_name||'.'||:TBL_name||'
            SELECT
                AU_NUM
                , OUTLET_ID
                , sale_wk
                , COUNT(DISTINCT account) AS accounts
                , SUM(widgets) AS widgets
            FROM
                mydb.tbl1
            GROUP BY 1,2,3;');
ELSE
        CALL DBC.SysExecSQL('
        CREATE MULTISET TABLE '||:DB_name||'.'||:TBL_name||' AS (
        SELECT
            AU_NUM
            , OUTLET_ID
            , sale_wk
            , COUNT(DISTINCT account) AS accounts
            , SUM(widgets) AS widgets
        FROM
            mydb.tbl1
        GROUP BY 1,2,3
        ) WITH DATA
        PRIMARY INDEX(AU_NUM, sale_wk);');
END IF;
END;

 


Accepted Solutions
Senior Apprentice

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

That's a common problem when you create macros/procedures/views across databases, the owning database needs a right WITH GRANT OPTION (otherwise security might be broken ). In your case you can probably change the SQL SECURITY option to INVOKER, see Rules for SQL Procedure Privileges

CREATE PROCEDURE mydb.table_exists_proc (IN DB_name VARCHAR(128), IN TBL_name VARCHAR(128)) 
SQL SECURITY INVOKER
BEGIN
   IF EXISTS(SELECT 1 FROM dbc.tablesV WHERE DatabaseName =:DB_name AND TABLENAME = :TBL_name)

I changed the IN variables to VarChar(128) which is the maximum size of an object name (less important) and switched to dbc.TablesV (very important because the old deprecated dbc.Tables truncates all object names to 30 characters and your TBL_name was a VarChar(50)) 

 

 

1 ACCEPTED SOLUTION
9 REPLIES
Teradata Employee

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

One doesn't Explain a procedure; one Explains SQL DML.  It is possible to turn on Explain tracing in Query Logging, run the procedure, and then see the Explains for each SQL statement in the procedure.

Enthusiast

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

I was able to successfully create the procedure. Now, when I call the procedure I am getting the following error:
"Statement 2: CALL Failed. 3803:  DDD_TABLE_EXISTS_PROC:Table 'ddd_start_right_metrics' already exists. "

 

The table does exist, so I was expecing the procedure to evaluate the snippet below as True, and then execute the INSERT INTO statement section. 

IF EXISTS(SELECT * FROM dbc.tables WHERE DatabaseName =''''||:DB_name||'''' AND TABLENAME = ''''||:TBL_name||'''') THEN

 Instead, it seems to be branching to the 'THEN' section and attempting to create the table. If I execute just the SELECT statement from within the EXISTS() check, the query returns a single row of 1. So I'm not figuring out why the IF statement isn't evaluating to True.

 

This is the CALL on the proceude: 

 

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 887 StartFragment: 314 EndFragment: 855 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

CALL MYDB.ddd_table_exists_proc ('MYDB','ddd_start_right_metrics');
Teradata Employee

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

I don't know offhand, but I have always used Select * with Exists.  Can you try "If Exists (Select * from ...)"  instead of Select 1?  Or Select Tablename?

Teradata Employee

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

On the other hand, I'm not sure you can say, "IF EXISTS" in SPL.  You might have to Select count(*) into :<variable-name> and then test IF :<variable-name> > 0 then ....

Enthusiast

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

I had considered this, but if the table has not been created yet won't the procedure produce and error and fail if I try to do a SELECT COUNT(*) FROM table_I_want_to_check_existence_on?

Teradata Employee

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

Sorry I wasn't clear.  I meant:

Select COUNT(*) from dbc.tables WHERE DatabaseName =''''||:DB_name||'''' AND TABLENAME = ''''||:TBL_name||'''') into :<var-name>.

 

If DBC.tables doesn't exist we have a bigger problem!

Senior Apprentice

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

There's no need for adding quotes in 

IF EXISTS(SELECT 1 FROM dbc.tables WHERE DatabaseName =''''||:DB_name||'''' AND TABLENAME = ''''||:TBL_name||'''')

as you pass parameters. This should be

IF EXISTS(SELECT 1 FROM dbc.tables WHERE DatabaseName =:DB_name AND TABLENAME = :TBL_name)

 

Enthusiast

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

Ok - that seemed to work and then IF condition is correctly evaluating to true so I am going to accept this as the answer.

 

As a follow up though, I am still getting en error (below).  The database (and the PROCEDURE itself) where I want to either CREATE the table or INSERT INTO the existing table sits in a different database (a more permanent storage space) than the table that is being selected from in both the "IF" and the "ELSE" parts of the procedure. Is there a way to create the procedure or add permissions so it has the appropriate select access to the other database? Or must both tables involved be residing within the same database?

 

"An owner referenced by user does not have SELECT access to OTHERDB.select_from_table

Senior Apprentice

Re: Procedure to conditionally CREATE TABLE or INSERT INTO depending on whether or not the table exi

That's a common problem when you create macros/procedures/views across databases, the owning database needs a right WITH GRANT OPTION (otherwise security might be broken ). In your case you can probably change the SQL SECURITY option to INVOKER, see Rules for SQL Procedure Privileges

CREATE PROCEDURE mydb.table_exists_proc (IN DB_name VARCHAR(128), IN TBL_name VARCHAR(128)) 
SQL SECURITY INVOKER
BEGIN
   IF EXISTS(SELECT 1 FROM dbc.tablesV WHERE DatabaseName =:DB_name AND TABLENAME = :TBL_name)

I changed the IN variables to VarChar(128) which is the maximum size of an object name (less important) and switched to dbc.TablesV (very important because the old deprecated dbc.Tables truncates all object names to 30 characters and your TBL_name was a VarChar(50))