• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Database Quick Start
  5. Creating and Managing Tables
  6. Inserting Data to a Table

Inserting Data to a Table

A new table is empty. You need to insert data to the table before using it. This section describes how to insert a row or multiple rows of data using the INSERT statement and to insert data from a specified table. For details about how to insert a large amount of data to a table in batches, see Data Import.

Context

The length of a character on the server and client may vary by the character sets they use. A string entered on the client will be processed based on the server's character set, so the result may differ from expected.

Table 1 Output based on the character sets of client and server

Procedure

Same Encoding Between Server and Client

Different Encoding Between Server and Client

No operations are performed to the string while it is saved and read.

Your expected result is returned.

If the encoding for input and output on the client is the same, your expected result will be returned.

Operations (such as executing string functions) are performed to the string while it is saved and read.

Your expected result is returned.

The result may differ from what you expected, depending on the operations you performed.

Overlong strings are truncated during saving.

Your expected result is returned.

If the character encoding length is the same as the character set, and unexpected result may occur if they are different.

More than one of the preceding operations can be performed to a string. For example, if the character sets of the client and server are different, a string may be processed by functions and then truncated. In this case, the result will also be unexpected. For details, see Table 2.

NOTE:

Long strings are truncated only if DBCOMPATIBILITY is set to TD (compatible with Teradata) and td_compatible_truncation is set to on.

Run the following commands to create table1 and table2 to be used in the example:

CREATE TABLE table1(id int, a char(6), b varchar(6),c varchar(6)) ;
CREATE TABLE table2(id int, a char(20), b varchar(20),c varchar(20)) ;
Table 2 Examples

SN

Server Character Set

Client Character Set

Whether Automatic Truncation Is Enabled

Examples

Result

Description

1

SQL_ASCII

UTF8

Yes

INSERT INTO table1 VALUES(1,reverse('123AA78'),reverse('123AA78'),reverse('123AA78'));
id |a|b|c
----+------+------+------
1 | 87| 87| 87

Character sets used by the server and client are different. After a string is reversed on the server and truncated, character A is displayed in multiple bytes and the result is incorrect.

2

SQL_ASCII

UTF8

Yes

INSERT INTO table1 VALUES(2,reverse('123A78'),reverse('123A78'),reverse('123A78'));
id |a|b|c
----+------+------+------
2 | 873| 873| 873

A string is automatically truncated after being reversed. Therefore, the result is unexpected.

3

SQL_ASCII

UTF8

Yes

INSERT INTO table1 VALUES(3,'87A123','87A123','87A123');
id |a|b|c
----+------+------+------
3 | 87A | 87A | 87A

The column length in string type is an integer multiple of its length in client character encoding. Therefore, the result is correct after truncation.

4

SQL_ASCII

UTF8

No

INSERT INTO table2 VALUES(1,reverse('123AA78'),reverse('123AA78'),reverse('123AA78'));
INSERT INTO table2 VALUES(2,reverse('123A78'),reverse('123A78'),reverse('123A78'));
id |a|b|c
----+-------------------+--------+--------
1 | 87?321| 87?321 | 87?321
2 | 87321| 87321| 87321

Similar to the first example, a character set composed of multi-byte characters no longer indicates the original characters after being reversed.

Procedure

Insert data to an existing table. For details about how to create a table, see Creating a Table.

  • Insert a row to table customer_t1:
    Column values are arranged in the same order as the columns in the table and are separated by commas (,). Generally, column values are text values (constants). Scalar expressions are also allowed.
    INSERT INTO customer_t1(c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', 'Grace');
    If you know the sequence of the columns in the table, you can obtain the same result without listing the columns. For example, the following statement generates the same result as the preceding statement:
    INSERT INTO customer_t1 VALUES (3769, 'hello', 'Grace');
    If you do not know some of the values, you can omit them. If no value is specified for a column, the column is set to the default value. For example:
    INSERT INTO customer_t1 (c_customer_sk, c_first_name) VALUES (3769, 'Grace');
    
    INSERT INTO customer_t1 VALUES (3769, 'hello');
    You can also specify the default value for a column or row:
    INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', DEFAULT);
    
    INSERT INTO customer_t1 DEFAULT VALUES;
  • To insert multiple rows to a table, run the following command:
    INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES 
        (6885, 'maps', 'Joes'),
        (4321, 'tpcds', 'Lily'),
        (9527, 'world', 'James');

    You are advised to use this command to quickly insert multiple data records.

  • Assume that you have created a backup table customer_t2 for table customer_t1. To insert data from customer_t1 to customer_t2, run the following statements:
    CREATE TABLE customer_t2
    (
        c_customer_sk             integer,
        c_customer_id             char(5),
        c_first_name              char(6),
        c_last_name               char(8)
    ) ;
    
    INSERT INTO customer_t2 SELECT * FROM customer_t1;
    NOTE:

    If implicit conversion is not implemented between the column data types of the specified table and those of the current table, the two tables must have the same column data types when data is inserted from the specified table to the current table.

  • Delete a backup file.
    DROP TABLE customer_t2 CASCADE;
    NOTE:

    If the table to be deleted is dependent on other tables, you need to delete its dependent tables first.