Inserting Data to a Table

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.

More than one of the preceding operations can be performed on a string. For example, if the character sets of the client and server are different, a string may be processed and then truncated. In this case, the result will also be unexpected. 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 statements to create tables table1 and table2 to be used in the examples:

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)) ;

1

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

1
2
3

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

1

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

1
2
3

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

1

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

1
2
3

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

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

Common Operations

You need to create a table before inserting data to it. For details about how to create a table, see Creating and Managing Tables.

• Insert a row to table customer_t1:
  Data values are arranged in the same order as the columns 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 columns in the table, you can obtain the same result without listing these columns. 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 column, the column is set to the default value. The following shows an 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 of a column 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 command to improve efficiency.

• Insert data to a table from a specified table. For example, run the following statement to insert the data of table customer_t1 to the backup table customer_t2.

  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 table.

  1	DROP TABLE customer_t2 CASCADE;

  

  If the table to be deleted is associated with other tables, delete the associated tables first.