Using BTEQ Scripts to Create Database Objects - Teradata Database

Basic Teradata Query Utility (BTEQ) is a general-purpose, command-based application that allows users to:

Perform administrative tasks, including creation of database objects such as databases, tables, and views, using SQL DCL, DDL, and DML requests.

Format reports for both print and screen output.

You can automate the creation of tables, views, and other database objects using BTEQ scripts.

The following example procedure uses a BTEQ script to create a sample table.

1 Create a text file named Create_Emp_Table.sql with the following contents:

   .LOGON tdpid/UserName,Password

   CREATE SET TABLE Tables_Database.Employee, FALLBACK

   (Associate_Id INTEGER,

    Associate_Name CHAR(25),

    Salary DECIMAL(8,2),

    DOB DATE,

    Job_Title VARCHAR(25),

    Dept_No SMALLINT,

    Marital_Status CHAR,

    No_Of_Dependents BYTEINT)

   UNIQUE PRIMARY INDEX (Associate_Id);

   .quit

where:

tdpid is the name by which the system is known to the network.

UserName is the name used to log on to Teradata, such as DBADMIN.

Password is the password associated with UserName.

2 Create a batch file named Create_Emp.bat with the following contents:

   bteq < Create_Emp_Table.sql > Create_Emp_Table.log 2>&1

where:

2>&1

redirects the error messages to Create_Emp_Table.log to be printed along with the output messages.

3 Place Create_Emp.bat and Create_Emp_Table.sql in the same folder.

4 Execute Create_Emp.bat to create the Employee table in the database, Tables_Database.

Create_Emp.bat invokes BTEQ using Create_Emp_Table.sql as the input file. BTEQ executes the commands and statements in the Create_Emp_Table.sql file and writes its output and any errors to the Create_Emp_Table.log file.

Reference Information

Information on...	Is available in...
starting and exiting BTEQ	Basic Teradata Query Reference
running batch jobs to submit BTEQ commands and Teradata SQL	Basic Teradata Query Reference