Working with Databases-Inserting Data to Tables
Tags: opengauss, database
Published: 2023-03-13 09:59:32

A new table contains no data. 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 command 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 Importing Data.

**Background**

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 Comparison of character set output between the client and server

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/up28brjw6kojc39blk36.png)

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 and then truncated. In this case, the result will also be unexpected. For details, see Table 2.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cuzzn1o5v6u5ovyga6zo.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d1pfta7kwc9cqwyyuglo.png)

Procedure
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, column values are text values (constants). But column values can also be scalar expressions.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ljxywdo915v09uoex55u.png)

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 command generates the same result as the preceding command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/frxuqgaodfwmhm0aw8fs.png)

If you do not know some of the column values, you can omit them. If no value is specified for a column, the column is set to the default value. For example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xeszmyc4cmkugpalrybu.png)

You can also specify the default value of a column or row:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/awn63kujjhkv0hozl39j.png)

**·** To insert multiple rows to a table, run the following command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kzal8zpzlajk877513xm.png)

You can also insert multiple rows by running the command for inserting one row for multiple times. However, you are advised to run this command 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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xajgqvit834f02dwuq46.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n1nmtojgika07uxo2501.png)

**·** To delete a backup file, run the following command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/akpd6xqlnsvi87afh56h.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qvc7a16r7zkxo3yxnxjd.png)