AnalyticDB:INSERT INTO

Syntax

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

Parameters

• IGNORE: ignores the insertion of the row that has a duplicate primary key value. This parameter is optional.

• column_name: the name of the column. This parameter is optional.

• query: the SELECT statement whose query results can be inserted into the table.

Usage notes

If you do not specify column names, columns in the data to be inserted must be arranged in the same order as the columns that are specified in the CREATE TABLE statement.

Examples

Create tables named customer and courses.

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 row of data into 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');                  

• Execute the INSERT INTO ... ON DUPLICATE KEY UPDATE... statement to insert a row of data into the customer table.

  INSERT INTO customer 
  values
  (002367,'Alan','13900001234','Hangzhou',0,'987300','West Lake','Cloud Town',23,'2018-03-02 10:00:00'); 
  ON DUPLICATE KEY
  UPDATE age=23;

• Insert multiple rows of data into 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','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 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 row of data into the courses table.

  INSERT INTO courses (name,submission_date) values("Jams",NOW());                

• For information about examples of INSERT query, see INSERT SELECT FROM.