Create Database Objects with BTEQ Scripts | Teradata Vantage - 17.00 - 17.05 - Using BTEQ Scripts to Create Database Objects - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
ujp1472240543947

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

  • 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,
    (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

    Example:

    2>&1

    This example 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.