Help Center> Data Warehouse Service> Getting Started> Database Quick Start> Creating and Managing Tables> Inserting Data to a Table
View PDF

Inserting Data to a Table

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. If a large amount of data needs to be imported to the table in batches, see Import Modes.

Background

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

Table 1 Comparison between the outputs of character sets configured for the client and server

Procedure

Same Encoding Between Server and Client

Different Encoding Between Server and Client

No operation is performed on the character string during storage and extraction.

Output the expected result.

Output the expected result (the entered client encoding format must be the same as the displayed one).

Some operations are performed on the character string during storage and extraction (such as operations performed on the character string function).

Output the expected result.

Unexpected results may be generated based on specific operations on character strings.

Ultra-long character strings are truncated during the storage process.

Output the expected result.

If the character encoding length in the character set is inconsistent, unexpected results may be generated.

The effect of the preceding character string function operations and automatic truncation can be overlapped. For example, if the character set of the client is different from that of the server, the character string operation is performed, and the character string is truncated, the character string is truncated after the character string is processed. As a result, unexpected results are generated. For details, see Table 2.

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:

1
2
 
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 Example

ID

Character Set of the Server

Character Set of the Client

Enable Automatic Truncation

Example

Result

Description

1

SQL_ASCII

UTF8

Yes

 
1
 
INSERT INTO table1 VALUES(1,reverse('123AA78'),reverse('123AA78'),reverse('123AA78'));

1
2
3
 
id |a|b|c
----+------+------+------
1 | 87| 87| 87

Character strings are truncated after being reversed on the server. Because the character sets of the server and client are different, character A is represented by multiple bytes on the client. As a result, the result is abnormal.

2

SQL_ASCII

UTF8

Yes

 
1
 
INSERT INTO table1 VALUES(2,reverse('123A78'),reverse('123A78'),reverse('123A78'));

1
2
3
 
id |a|b|c
----+------+------+------
2 | 873| 873| 873

Character strings are automatically truncated after being reversed. Therefore, an unexpected result is generated.

3

SQL_ASCII

UTF8

Yes

 
1
 
INSERT INTO table1 VALUES(3,'87A123','87A123','87A123'

1
2
3
 
id |   a   |   b   |   c
----+-------+-------+-------
  3 | 87A1 | 87A1 | 87A1

The field length of the character string type is an integer multiple of the character encoding length of the client. Therefore, the result is normal after the truncation.

4

SQL_ASCII

UTF8

No

 
1
2
 
INSERT INTO table2 VALUES(1,reverse('123AA78'),reverse('123AA78'),reverse('123AA78'));
INSERT INTO table2 VALUES(2,reverse('123A78'),reverse('123A78'),reverse('123A78'));

1
2
3
4
 
id |a|b|c
----+-------------------+--------+--------
1 | 87 321| 87 321 | 87 321
2 | 87321| 87321| 87321

Similar to example 1, the original character is no longer represented after the multi-byte character inversion.

Procedure

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

  • Insert a row to table customer_t1:
    Field values are arranged in the same order as they are arranged in the table and are separated by commas (,). Generally, they are text values (constants). Scalar expressions are also allowed. 
    1
    		 
    INSERT INTO customer_t1(c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', 'Grace');
    If you know the sequence of the fields in the table, you can obtain the same result without listing them. For example, the following statement generates the same result as the preceding statement: 
    1
    		 
    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 field, the field is set to the default value. Example: 
    1
2
3
    		 
    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 field or row: 
    1
2
3
    		 
    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, run the following statement: 
    1
2
3
4
    		 
    INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES 
    (6885, 'maps', 'Joes'),
    (4321, 'tpcds', 'Lily'),
    (9527, 'world', 'James');

    You can also insert multiple rows by running the statement for inserting one row for multiple times. However, you are advised to run this single statement to improve efficiency.

  • 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: 
    1
2
3
4
5
6
7
8
9
    		 
    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;

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

  • Delete a backup file. 
    1
    		 
    DROP TABLE customer_t2 CASCADE;

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