BTEQ was the first utility and query tool for Teradata. BTEQ can be used as a Query tool, to load data a row at a time into Teradata and to export data off of Teradata a row at a time.
The features of BTEQ:
- BTEQ can be used to submit SQL in either a batch or interactive environment.
- BTEQ gives the outputs in a report format, where Queryman outputs data in a format more like a spreadsheet.
- As said Bteq is an excellent tool for importing and exporting data.
Export DATA
This is set by .EXPORT DATA.
Generally, users will export data to a flat file format.This is called Record Mode or DATA mode.
If the data has no headers or white space between the data contained in each column and the data is written to the file in a normal format.
Export INDICDATA
This is set by .EXPORT INDICDATA.
This mode is used to export data with extra indicator bytes to indicate NULLs in column for a row.
Export REPORT
This is set by .EXPORT REPORT
In this mode the output of BTEQ export would return the column headers for the fields, white space, expanded packed or binary data.
Its just looks like a report with column headers and data.
Export DIF
This called as Data Interchange Format, which allows users to export data from Teradata to be directly utilized for spreadsheet applications like Excel, FoxPro and Lotus.
Below is the example of BTEQ IMPORT Script.We are taking data form a flat file from C:\TEMP\EMPDATA.txt and importing records into Empdb.Emp_Table.
.LOGON USERNAME/PWD;
.IMPORT DATA FILE=C:\TEMP\EMPDATA.txt, SKIP=2
.QUIET ON
.REPEAT*
USING (EMP_NUM INTEGER
,EMP_NAME VARCHAR(20)
,EMP_PHONE VARCHAR(10)
)
INSERT INTO Empdb.Emp_Table
VALUES( :EMP_NUM
,:EMP_NAME
,:EMP_PHONE
);
.QUIT
.LOGOFF
Below is a review of those commands for the above example.
- QUIET ON limits BTEQ output to reporting only errors and request processing statistics.
- REPEAT * causes BTEQ to read a specified number of records or until EOF. The default is one record. Using REPEAT 100 would perform the loop 100 times.
- The USING defines the input data fields and their associated data types coming from the host.
