All Products
Search
Document Center

AnalyticDB for MySQL:INSERT INTO

Last Updated:Dec 29, 2023

You can execute the INSERT INTO statement to insert data into a table. If a row of data that you want to insert has a duplicate primary key value, the insertion is ignored. This statement is equivalent to INSERT IGNORE 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.