You can execute the INSERT INTO statement to insert data to a table. If a primary key is the same as an existing one in the records, the new record is not inserted. This statement is equivalent to the INSERT IGNORE INTO statement.

Syntax

INSERT [IGNORE] 
    INTO table_name 
    [( column_name [, ...] )]
    [VALUES]
    [(value_list[, ...])]
    [query];

Parameters

  • IGNORE: optional. Specifies that a new record is not inserted if the primary key of the record is the same as that of an existing record.
  • column_name: optional. The name of the column.
  • query: inserts one or more records queried from another table to this table.

Precautions

If no column names are specified, the sequence of the columns in the data to be inserted must be the same as that specified in the CREATE TABLE statement. For more information, see CREATE TABLE.

Example

Create the customer and courses tables.

CREATE TABLE customer (
  customer_id bigint NOT NULL COMMENT 'Customer ID',
  customer_name varchar NOT NULL COMMENT 'Customer name',
  phone_num bigint NOT NULL COMMENT 'Phone number',
  city_name varchar NOT NULL COMMENT 'City',
  sex int NOT NULL COMMENT 'Gender',
  id_number varchar NOT NULL COMMENT 'ID card number',
  home_address varchar NOT NULL COMMENT 'Home address',
  office_address varchar NOT NULL COMMENT 'Office address',
  age int NOT NULL COMMENT 'Age',
  login_time timestamp NOT NULL COMMENT 'Logon time',
  PRIMARY KEY (login_time,customer_id,phone_num)
)
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT 'Customer information table';           
CREATE TABLE courses(
id bigint AUTO_INCREMENT PRIMARY KEY,
name varchar(20) NOT NULL,
grade varchar(20) default 'Grade 3',
submission_date timestamp
)
DISTRIBUTED BY HASH(id)
  • Insert a record to the customer table. 
    INSERT INTO customer(customer_id,customer_name,phone_num,city_name,sex,id_number,home_address,office_address,age,login_time) 
values
(002367,'Alan','13900001234','Hangzhou',0,'987300','West Lake','Cloud Town',23,'2018-03-02 10:00:00');
  • Insert multiple records to the customer table. 
    INSERT INTO  customer(customer_id,customer_name,phone_num,city_name,sex,id_number,home_address,office_address,age,login_time) 
values
(002367,'Tom','133900001234','Hangzhou',0,'987300','West Lake','Cloud Town',23,'2018-03-02 10:00:00'),(002368,'Alex','13900001111','Hangzhou',0,'987300','West Lake','Cloud Town',28,'2018-08-01 11:00:00'),(002369,'Eric','13900002222','Hangzhou',1,'987300','West Lake','Cloud Town',35,'2018-09-12 08:11:00');
  • Insert multiple records to the customer table without specifying the column names. 
    INSERT INTO 
customer values
(002367,'Tom','13900001234','Hangzhou',0,'987300','West Lake','Cloud Town',23,'2018-03-02 10:00:00'),(002368,'Alex','13900001111','Hangzhou',0,'987300','West Lake','Cloud Town',28,'2018-08-01 11:00:00'),(002369,'Eric','13900002222','Hangzhou',1,'987300','West Lake','Cloud Town',35,'2018-09-12 08:11:00');
  • Insert a record to the courses table. 
    insert into courses (name,submission_date) values("Jams",NOW());
  • For more information about examples of INSERT query, see INSERT SELECT FROM.