Drop and Create Table if table exists

Database
KS
N/A

Drop and Create Table if table exists

Hi Gurus : I am trying to check if a Table exists in a database THEN DROP IT AND THEN CREATE the table else if the table never exists then simply create the table with data - Import from a delimited CSV file. I am tryning this using BTEQ scripts. PS: I do see similar posts in the community but failed to see any concrete answers, please help me with some sample codes...I am new in this world of things...:) Thanks KS
7 REPLIES
Teradata Employee

Re: Drop and Create Table if table exists

In my bteq scripts I just drop the table whether it exists or not - I like to keep things simple whenever possible.  You might get a "table does not exist" message but it won't stop the script.

KS
N/A

Re: Drop and Create Table if table exists

Can you please share a sample code , also along with Insert data from a csv file ... I want to try your approach once.. Thanks :)

Teradata Employee

Re: Drop and Create Table if table exists

Please check the Bteq manual from info.teradata.com for details, but here is a start off the top of my head:

 

drop table table1;
.import vartext ',' file = test.csv;
.repeat *
using (col1 varchar(256), col2 varchar(5000))
insert into table1 (c1, c2) values (:col1, :col2)
;

 

If the .csv file contains quoted fields, you will need something fancier like an AXSMOD - I believe there is one in the TTU distribution that handles those.  Also, if this is a large file you might consider more sessions (again, see the bteq manual), or try using the TPT Load utility.

KS
N/A

Re: Drop and Create Table if table exists

KS
N/A

Re: Drop and Create Table if table exists

It would be really helpful if we can have - Any Sample Working Code for DROP and CREATE Table that works.. 

Teradata Employee

Re: Drop and Create Table if table exists

here is a BTEQ script that drops a table (if the table doesn't exist, it will throw an error but the script will continue), creates the table, and then inserts into that table from a source CSV file.

.logon hostname/username, password;

.HANG 3   --wait 3 seconds

.IMPORT VARTEXT ',' FILE = C:\calendar_output.csv

DROP TABLE STAGING_DB.MFG_CALENDAR_TEMP;
CREATE SET TABLE STAGING_DB.MFG_CALENDAR_TEMP
     (
      COMPANY_ID VARCHAR(25) CHARACTER SET LATIN CASESPECIFIC,
      PRODUCTION_TEST_IND SMALLINT,
      PRODUCTION_TEST_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      CALENDAR_DT DATE FORMAT 'MM/DD/YYYY',
      DAY_OF_WK_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC,
      FSCL_YR_WK_NBR SMALLINT,
      HOLIDAY_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC,
      SHIFT_NBR SMALLINT,
      LINE_DESC VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC,
      SCHEDULE_TYPE_DESC VARCHAR(25) CHARACTER SET LATIN CASESPECIFIC,
      SHIFT_START_TM TIME(0),
      SHIFT_END_TM TIME(0),
      STATION_BREAK1_STRT_TM TIME(0),
      STATION_BREAK1_END_TM TIME(0),
      STATION_BREAK2_STRT_TM TIME(0),
      STATION_BREAK2_END_TM TIME(0),
      STATION_BREAK3_STRT_TM TIME(0),
      STATION_BREAK3_END_TM TIME(0),
      CALENDAR_UPDATE_DTTM TIMESTAMP(0),
      UPDATED_BY VARCHAR(30) CHARACTER SET LATIN CASESPECIFIC)
PRIMARY INDEX ( COMPANY_ID ,CALENDAR_DT ,DAY_OF_WK_IND ,FSCL_YR_WK_NBR ,
HOLIDAY_IND ,SHIFT_NBR ,LINE_DESC );

.REPEAT * PACK 250
USING
(
        COMPANY_ID VARCHAR(25)
        ,PRODUCTION_TEST_IND VARCHAR(10)
        ,PRODUCTION_TEST_DESC VARCHAR(100)
        ,CALENDAR_DT VARCHAR(10)
        ,DAY_OF_WK_IND VARCHAR(2)
        ,FSCL_YR_WK_NBR VARCHAR(10)
        ,HOLIDAY_IND VARCHAR(2)
        ,SHIFT_NBR VARCHAR(10)
        ,LINE_DESC VARCHAR(50)
        ,SCHEDULE_TYPE_DESC VARCHAR(25)
        ,SHIFT_START_TM VARCHAR(10)
        ,SHIFT_END_TM VARCHAR(10)
        ,STATION_BREAK1_STRT_TM VARCHAR(10)
        ,STATION_BREAK1_END_TM VARCHAR(10)
        ,STATION_BREAK2_STRT_TM VARCHAR(10)
        ,STATION_BREAK2_END_TM VARCHAR(10)
        ,STATION_BREAK3_STRT_TM VARCHAR(10)
        ,STATION_BREAK3_END_TM VARCHAR(10)
)
INSERT INTO STAGING_DB.MFG_CALENDAR_TEMP
   (
        COMPANY_ID,
        PRODUCTION_TEST_IND,
        PRODUCTION_TEST_DESC,
		    CALENDAR_DT,
		    DAY_OF_WK_IND,
		    FSCL_YR_WK_NBR,
		    HOLIDAY_IND,
		    SHIFT_NBR,
		    LINE_DESC,
		    SCHEDULE_TYPE_DESC,
		    SHIFT_START_TM,
		    SHIFT_END_TM,
		    STATION_BREAK1_STRT_TM,
		    STATION_BREAK1_END_TM,
		    STATION_BREAK2_STRT_TM,
		    STATION_BREAK2_END_TM,
		    STATION_BREAK3_STRT_TM,
		    STATION_BREAK3_END_TM,
		    CALENDAR_UPDATE_DTTM,
		    UPDATED_BY
   )
VALUES
   (
        :COMPANY_ID,
        CAST(:PRODUCTION_TEST_IND as INTEGER),
        :PRODUCTION_TEST_DESC,
		    cast(:CALENDAR_DT as DATE FORMAT 'YYYY-MM-DD'),
		    :DAY_OF_WK_IND,
		    CAST(:FSCL_YR_WK_NBR as SMALLINT),
		    :HOLIDAY_IND,
		    CAST(:SHIFT_NBR as SMALLINT),
		    :LINE_DESC,
		    :SCHEDULE_TYPE_DESC,
		    CAST(:SHIFT_START_TM as TIME(0)),
		    CAST(:SHIFT_END_TM as TIME(0)),
		    CAST(:STATION_BREAK1_STRT_TM as TIME(0)),
		    CAST(:STATION_BREAK1_END_TM as TIME(0)),
		    CAST(:STATION_BREAK2_STRT_TM as TIME(0)),
		    CAST(:STATION_BREAK2_END_TM as TIME(0)),
		    CAST(:STATION_BREAK3_STRT_TM as TIME(0)),
		    CAST(:STATION_BREAK3_END_TM as TIME(0)),
		    current_timestamp(0),
		    USER
   );


.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

Re: Drop and Create Table if table exists

Use this Scripts

 

SyntaxEditor Code Snippet

IF EXISTS(SELECT 1 FROM dbc.tables WHERE databasename = databasename AND TABLENAME = tablename) THEN
        CALL DBC.SysExecSQL('DROP TABLE ' || databasename ||'.'|| tablename);
ELSE
        do something        
END IF;