• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. INSERT

INSERT

Function

INSERT inserts new rows into a table.

Precautions

  • You must have the INSERT permission on a table in order to insert into it.
  • Use of the RETURNING clause requires the SELECT permission on all columns mentioned in RETURNING.
  • If you use the query clause to insert rows from a query, you need to have the SELECT permission on any table or column used in the query.
  • When you connect to a database compatible to Teradata and td_compatible_truncation is on, a long string will be automatically truncated. If later INSERT statements (not involving foreign tables) insert long strings to columns of char- and varchar-typed columns in the target table, the system will truncate the long strings to ensure no strings exceed the maximum length defined in the target table.
    NOTE:

    If inserting multi-byte character data (such as Chinese characters) to database with the character set byte encoding (SQL_ASCII, LATIN1), and the character data crosses the truncation position, the string is truncated based on its bytes instead of characters. Unexpected result will occur in tail after the truncation. If you want correct truncation result, you are advised to adopt encoding set such as UTF8, which has no character data crossing the truncation position.

Syntax

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ ( column_name [, ...] ) ]
    { DEFAULT VALUES
    | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] 
    | query }
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

Parameter Description

  • WITH [ RECURSIVE ] with_query [, ...]

    The WITH clause allows you to specify one or more subqueries that can be referenced by name in the primary query, equal to temporary table.

    If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

    The detailed format of with_query is as follows: with_query_name [ (column_name [,...]) ] AS

    ( {select | values | insert | update | delete} )

    -- with_query_name specifies the name of the result set generated by a subquery. Such names can be used to access the result sets of

    subqueries in a query.

    column_name specifies the column name displayed in the subquery result set.

    Each subquery can be a SELECT, VALUES, INSERT, UPDATE or DELETE statement.

  • table_name

    Specifies the name of the target table.

    Value range: an existing table name

  • column_name

    The name of a column in a table.

    • The column name can be qualified with a subfield name or array subscript, if needed.
    • Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or NULL if there is none. (Inserting into only some fields of a composite column leaves the other fields null.)
    • The target column names column_name can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.
    • The target columns are the first N column names, if there are only N columns supplied by the value clause or query.
    • The values supplied by the value clause or query are associated with the explicit or implicit column list left-to-right.

    Value range: an existing column name

  • expression

    Specifies an expression or a value to assign to the corresponding column.

    • If single-quotation marks are inserted in a column, the single-quotation marks need to be used for escape.
    • If the expression for any column is not of the correct data type, automatic type conversion will be attempted. If the attempt fails, data insertion fails and the system returns an error message.
  • DEFAULT

    All columns will be filled with their default values. The value is NULL if no specified default value has been assigned to it.

  • query

    A query (SELECT statement) that supplies the rows to be inserted.

  • RETURNING

    Returns the inserted rows. The syntax of the RETURNING list is identical to that of the output list of SELECT.

  • output_expression

    An expression used to calculate the output of the INSERT command after each row is inserted.

    Value range: The expression can use any field in the table. Write * to return all columns of the inserted row(s).

  • output_name

    A name to use for a returned column.

    Value range: a string compliant with the naming convention.

Example

-- Create the tpcds.reason_t2 table:
CREATE TABLE tpcds.reason_t2
(
  r_reason_sk    integer,
  r_reason_id    character(16),
  r_reason_desc  character(100)
);

-- Insert a record into a table:
INSERT INTO tpcds.reason_t2(r_reason_sk, r_reason_id, r_reason_desc) VALUES (1, 'AAAAAAAABAAAAAAA', 'reason1');

-- Insert a record into a table. This command is equivalent to the last one.
INSERT INTO tpcds.reason_t2 VALUES (2, 'AAAAAAAABAAAAAAA', 'reason2');

-- Insert records into the table:
INSERT INTO tpcds.reason_t2 VALUES (3, 'AAAAAAAACAAAAAAA','reason3'),(4, 'AAAAAAAADAAAAAAA', 'reason4'),(5, 'AAAAAAAAEAAAAAAA','reason5');

-- Insert records whose r_reason_sk in the tpcds.reason table is less than 5:
INSERT INTO tpcds.reason_t2 SELECT * FROM tpcds.reason WHERE r_reason_sk <5;

-- Delete the tpcds.reason_t2 table:
DROP TABLE tpcds.reason_t2;