Teradata Sample Scripts - BTEQ

Advantages of BTEQ Import:

1. Bteq import is light and uses less system resources as compared to its heavy-duty cousins.

2. Bteq import allows manipulation of the incoming data during the load. See the example above, where we have divided the :IN_ADD_DWN_PMT_AM 

3. The PACK option helps us to load multiple rows based on the value set.

Bteq Script for import data from .txt file to database:

Example script:
1).logon edw/developer1,test;

.IMPORT REPORT FILE = /home/dev1/TRAN.MAR262013.csv

.REPEAT * PACK 250

USING

(

         IN_REC_TYP            CHAR(2)

        ,IN_CUS_NO         CHAR(18)

        ,IN_FTY_NO          CHAR(7)

        ,IN_REG_NO             CHAR(3)

        ,IN_TRS_NO              CHAR(4)

        ,IN_TRS_DT               CHAR(10)

        ,IN_TRS_TM               CHAR(8)

        ,IN_APP_ID         CHAR(9)

        ,IN_AMT               CHAR(7)

        ,IN_ADD_DWN_PMT_AM CHAR(7)

        ,IN_FST_PMT_AM     CHAR(7)

)

INSERT INTO Database1.Pos_register

   (

         CUS_ID_NO

        ,IAN_TYP_CD

        ,FTY_NO

        ,REG_NO

        ,TRS_NO

        ,TRS_DT

        ,TRS_TM

        ,APP_ID

        ,AMT

        ,ADD_DWN_PMT_AM

        ,FST_PMT_AM

   )

VALUES

   (

        :IN_CUS_ID_NO

        ,'RS'

        ,TRIM(:IN_FTY_NO)

        ,TRIM(:IN_REG_NO)

        ,TRIM(:IN_TRS_NO)

        ,:IN_trs_dt (DATE,FORMAT'YYYY-MM-DD')

        ,:IN_trs_tm (TIME(0))

        ,NULLIF(:IN_APP_ID, '         ')

        ,CASE WHEN TRIM(:IN_LSE_AM) = ''

              THEN 0

         ELSE (TRIM(:IN_AMT))

         END

        ,CASE WHEN TRIM(:IN_ADD_DWN_PMT_AM/100) = ''

              THEN 0

         ELSE (TRIM(:IN_ADD_DWN_PMT_AM/100))

         END

        ,CASE WHEN TRIM(:IN_FST_PMT_AM) = ''

              THEN 0

         ELSE (TRIM(:IN_FST_PMT_AM/100))

         END

   );

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

2)

.logon tdpid/username,password;
.import vartext ',' file = c:\p\var.txt

database db_name;

.quiet on;

.repeat *;

using i_eid(integer),

           i_ename(varchar(30)),

           i_sal(dec(6,2)),

           i_grade(varchar(30)),

           i_dept(varchar(30))

insert into tab_name(eid,ename,sal,grade,dept)

values(:i_eid,:i_ename,:i_sal,:i_grade,:i_dept);

.logoff;

Note:-

1) In betq script every key word starts with '.' end with ';'................

Script fo Export data from database:

.logon tdpid/username,password;
database db_name;
.export report file=c:\p\hi.txt
.set retlimit 4
select * from tab_name;
.export reset;
.logoff;