TeraData

=  BTEQ IMPORT Example   = BTEQ can also read a file from the hard disk and incorporate the data into SQL to modify the contents of one or more tables. In order to do this processing, the name and record description of the file must be known ahead of time. These will be defined within the script file. Format of the IMPORT command: .IMPORT { FILE | DNAME } = [,SKIP=n] The script below introduces the IMPORT command with the Record ( ** DATA ** ) option. Notice the periods (.) at the beginning some of script lines. A period starting a line indicates a BTEQ command. If there is no period, then the command is an SQL command. The SKIP option is used when you wish to bypass the first records in a file. For example, a mainframe tape may have header records that should not be processed. Other times, maybe the job started and loaded a few rows into the table with a UPI defined. Loading them again will cause an error. So, you can skip over them using this option. The following example will use a Record ( ** DATA ** ) Mode format. The input of the imported data will populate the Employee_Table. ** . ** REPEAT * || ** Limit messages out. ** ** Loop in this script until end of records in file. ** ||  Figure 2-9 From the above example, a number of BTEQ commands were added to the import script. Below is a review of those commands. ·  .QUIET ON limits BTEQ output to reporting only errors and request processing statistics. Note: Be careful how you spell .QUIET, else forgetting the E becomes .QUIT and it will. ·  .REPEAT * causes BTEQ to read a specified number of records or until EOF. The default is one record. Using REPEAT 10 would perform the loop 10 times. ·  The USING defines the input data fields and their associated data types coming from the host. The following builds upon the IMPORT Record ( ** DATA ** ) example above. The example below will still utilize the Record ( ** DATA ** ) Mode format. However, this script will add a CREATE TABLE statement. In addition, the imported data will populate the newly created Employee_Profile table. ** . ** QUIT || || Figure 2-10 Notice that some of the scripts have a .LOGOFF and .QUIT. The .LOGOFF is optional because when BTEQ quits, the session is terminated. A logoff makes it a friendly departure and also allows you to logon with a different user name and password.
 * ** . ** SESSIONS 4 || ** Specify the number of SESSIONS to establish with Teradata ** ||
 * ** . ** LOGON CDW/sql01,whynot; || ** Logon to TERADATA ** ||
 * ** . ** IMPORT ** DATA  ** FILE = C:\EMPS.TXT, SKIP = 2  || ** Specify DATA mode, name the file to read “EMPS.TXT”, but skip the first 2 records. ** ||
 * ** . ** QUIET ON
 * [[image:http://www.coffingdw.com/utilities/image/img00363.gif width="327" height="97" caption="image\img00363.gif"]] || ** The USING Specifies the field in the input file and names them. ** ||
 * [[image:http://www.coffingdw.com/utilities/image/img00364.gif width="254" height="117" caption="image\img00364.gif"]] || ** Specify the insert parameters for the employee_table ** ||
 * [[image:http://www.coffingdw.com/utilities/image/img00365.gif width="216" height="98" caption="image\img00365.gif"]] || ** Substitutes data from the fields into the SQL command. ** ||
 * .QUIT || ** Exit the script after all data read and rows inserted. ** ||
 * ** . ** SESSIONS 2 || ** Specify the number of SESSIONS to establish with Teradata ** ||
 * .LOGON CSW/sql101.whynot || ** Logon to TERADATA ** ||
 * DATABASE SQL_Class; || ** Make the default database SQL_Class ** ||
 * [[image:http://www.coffingdw.com/utilities/image/img00366.gif width="356" height="175" caption="image\img00366.gif"]] || ** This statement will create the Employee_Profile table. ** ||
 * ** . ** IMPORT ** INDICDATA  ** FILE = C:\IND-EMPS.TXT  || ** This import statement specifies INDICDATA mode. The input file is from a LAN file called IND-EMPS.TXT. ** ||
 * ** . ** QUIET ON || ** Quiet on limits the output to reporting only errors and processing statistics. ** ||
 * ** . ** REPEAT 120 || ** This causes BTEQ to read the first 120 records from the file. ** ||
 * [[image:http://www.coffingdw.com/utilities/image/img00367.gif width="340" height="94" caption="image\img00367.gif"]] || ** The USING Specifies the parameters of the input file. ** ||
 * [[image:http://www.coffingdw.com/utilities/image/img00368.gif width="256" height="112" caption="image\img00368.gif"]] || Specify the insert parameters for the employee_profile.  ||
 * [[image:http://www.coffingdw.com/utilities/image/img00369.gif width="253" height="94" caption="image\img00369.gif"]] || ** Substitute the values to be inputted into the SQL command. ** ||
 * ** . ** LOGOFF