INSERT Command Teradata

The INSERT statement is used to put a new row into a table. A status is the only returned value from the database; no rows are returned to the user. It must account for all the columns in a table using either a data value or a NULL. When executed, the INSERT places a single new row into a table. Although it can run as a single row insert, primarily it is used in utilities like BTEQ, FastLoad, MultiLoad, TPump or some other application that reads a data record and uses the data to build a new row in a table.

The following syntax of the INSERT does not use the column names as part of the command. Therefore, it requires that the VALUES portion of the INSERT match each and every column in the table with a data value or a NULL.

INS[ERT] [ INTO ] <table-name>VALUES ( <literal-data-value1> [ ,<literal-data-value2> [ ... , <literal-data-valueN> ] ] ;

Note Using INS instead of INSERT is not ANSI compliant.

Since the column names are not used the INSERT sequences the data values column by column. Therefore, the INSERT must exactly match the sequence of the columns as defined by the CREATE TABLE. This includes a correct match on the data types as well.

This uses the following table called My_table, defined as:


An example of an INSERT statement might appear like this:

INSERT INTO My_table VALUES ( 'My character data', 124.56, 102587, , NULL, '2000- 12-31' ) ;

After the execution of the above INSERT, there is a new row with the first character data value of ‘My character data’ going into Column1, the decimal value of 124.56 into Column2, the integer 102587 into Column3, NULL values into Column4 and Column5, and a date into Column6.

The NULL expressed in the VALUES list is the literal representation for no data. However, the two commas (,,) that follow the positional value for Column3 also represent missing data. The commas are placeholders or delimiters for the data values. When no data value is coded, the end result is a NULL.

Unlike many of the columns in My_table, Column4 and Column5 are defined to allow a NULL. Any attempt to place a NULL into a column defined as NOT NULL, will cause an error and the row is not inserted. In Teradata, the default in a CREATE TABLE is to allow NULL. However, a NOT NULL can be used in the DDL to alter this default functionality.

There is another form of the INSERT statement that can be used when some of the data is not available. It allows for the missing values (NULL) to be eliminated from the list in the VALUES clause. It is also the best format when the data is arranged in a different sequence than the CREATE TABLE, or when there are more nulls (unknown values) than available data values.

The syntax of the second type of INSERT follows:

INS[ERT] [ INTO ]<table-name> ( <column-name1> [ ,<column-name2> [ ,<column-nameN> ] ] VALUES ( <literal-data-value1> [ ,<literal-data-value2> [,<literal-data-valueN> ] ] ;

This form of the INSERT statement could be used to insert the same row as the previous INSERT example. However, it might look like this:

The above statement incorporates both of the reasons to use this syntax. First, notice that the column names Column2 and Column1 have been switched, to match the data values. Also, notice that Column4 and Column5 do not appear in the column list, therefore they are assumed to be NULL. This is a good format to use when the data is coming from a file and does not match the order of the table columns.

The third form of the INSERT statement can be used to insert the same row as the previous INSERT. It might look like this:


Teradata now has the ANSI DEFAULT VALUES functionality. Although an INSERT statement could easily put a null value into a table column, it requires it to use the NULL reserved word or by omitting a value for that column(s) between commas.

The either of the next two INSERT statements may be used to build a row with no data values in My_table:

Since the Teradata release of V2R3, it is now easier to insert null values into all columns. The following INSERT can now be used:


Although all of these INSERT options build a row with all NULL values, it is probably not an option that is needed very often. For instance, if a table uses a UPI (Unique Primary Index) column, any of these above commands could only be executed one time. Once a NULL is the value stored in the PI column, it can be the only row (unique) with that value. Therefore, to get the most benefit from any of these INSERT options, the table should have a NUPI (Non-Unique Primary Index). Additionally, the table should have DEFAULT values defined using the CREATE TABLE. That way, the NULL values are replaced by the DEFAULT. Even then, all NULL or DEFAULT values in the column(s) of the PI all go to the same AMP. This provides an ability to insert many nulls, it also creates a very high potential for skewed data rows with all the nulls going to the same AMP. Its use is a bit limited.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics