Types of Import Operations - SQL Assistant

Teradata SQL Assistant for Microsoft Windows User Guide

Product
SQL Assistant
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-2430
lifecycle
previous
Product Category
Teradata Tools and Utilities

Types of Import Operations

There are three types of import operations:

Type 1: Parameterized Import

This form applies to CALL, EXECUTE, SELECT, INSERT, UPDATE, and DELETE statements only (with Microsoft Access, this form is used for INSERT only.) The parameter markers can only be used in place of data values and must not be enclosed in quotes.

There are four types of parameter markers in this case:

  • ? - The data for this parameter is read from the Import file. It is always a character string, and converts to a numeric value if necessary.
  • ?? - The data for this parameter is read from the Import file. It should contain a Hexadecimal string which can contain dashes between each byte value.
    For example: 414B43 or 41-4B-43.
  • ?B - The data for this parameter resides in a file that is in the same directory as the Import file. The import file contains only the name of the file to be imported. The contents of the file are loaded as a binary image.
  • ?C - The data for this parameter resides in a file that is in the same directory as the import file. The import file contains only the name of the file to be imported. Use this marker to load a text file into a CHAR or CLOB column.
  • For example:

    Insert Into TestTbl Values (?, ?, , 'Const', ??, ?B, ?C)

    In the example above:

  • The table must have a total of seven columns
  • A NULL character is inserted in column 3 for all rows
  • The word 'Const' is inserted in column 4 for all rows
  • The ?, ??, ?B, ?C characters are the parameter markers
  • The import file must have five data values per record
  • The 3rd parameter must be a hexadecimal string
  • The 4th parameter must be the name of a binary data file in the same directory
  • The 5th parameter must be the name of a text file in the same directory
  • Type 2: Direct Substitution

    This form applies to all other statements. Parameter markers can be used anywhere in the query. The data values are directly edited into the SQL at the parameter positions. Therefore, some parameter markers might need to be enclosed in quotes.

    Create User ? As Perm=0 Password=? Account=’?’

    Note: In Direct Substitution, only the “?” parameter is valid.

    If an IMPORT statement returns data, the results are written to a file whose name is formed by adding ‘.Log’ to the end of the import file name. If this file already exists, its contents are replaced with new results. A header record containing the first data value from the corresponding import record is written before each set of returned data.

    Type 3: Batch

    This form applies when the following conditions are met:

  • Teradata SQL Assistant is connected through Teradata.NET or Oracle.net, not ODBC
  • The query consists of a single INSERT, UPDATE, or DELETE statement
  • The statement must not contain any LOB parameters [?B or ?C].
  • The Batch size in the Import tab of the Options dialog box is not specified as one
  • If the Batch size is set to one, the Batch import mode is not used. For larger values, the data provider packs the data for the specified number of rows into a batch (as long as it fits into a 64k parcel), and executes one batch at a time.

    In general, the larger the batch size, the better the performance. With larger batch sizes:

  • Data for a single batch must fit into a 64k parcel
  • The entire Import file is loaded into memory
  • If a batch import results in an error, the entire batch is rolled back and Teradata SQL Assistant reports which record caused the error
  • If multiple records result in errors in a single batch, the indicated record might not be the first problem record in the file
  • The Silently discard rows that cause constraint violations option on the Import tab of the Options dialog box can be selected if the data is just a sample, or if loss of duplicates or other rows that cause constraint violations are not a problem. If this option is selected, all errors are ignored and Teradata SQL Assistant reports the number of rows that were imported and the number of rows discarded.