AnalyticDB:INSERT INTO - AnalyticDB

Inserts rows into a table. In AnalyticDB for MySQL, INSERT INTO behaves like INSERT IGNORE INTO: when an inserted row shares a primary key with an existing row, the conflicting row is silently ignored rather than overwritten.

Syntax

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

Parameters

Parameter	Required	Description
`IGNORE`	No	Skips any row whose primary key duplicates an existing row.
`column_name`	No	The name of the target column.
`query`	—	A `SELECT` statement whose results are inserted into the table.

Usage notes

If you omit column names, the values in your VALUES list must appear in the same order as the columns defined in the CREATE TABLE statement.

Examples

The following examples use two tables — customer and courses — defined as shown below.

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 single row with explicit column names

Specify each column name to make the insert order independent of the table definition.

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 a single row without column names

Omit column names when the value order matches the CREATE TABLE definition exactly.

INSERT INTO customer
VALUES
  (002367, 'Alan', '13900001234', 'Hangzhou', 0, '987300', 'West Lake', 'Cloud Town', 23, '2018-03-02 10:00:00');

Insert multiple rows

Group multiple rows into one statement to reduce round trips.

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 rows without specifying column names

When the value order matches the CREATE TABLE definition exactly, you can omit column names across multiple rows.

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

Update an existing row on duplicate key

ON DUPLICATE KEY UPDATE updates a specific column when an inserted row conflicts with an existing primary key, instead of ignoring the conflict.

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;

The default INSERT INTO behavior in AnalyticDB for MySQL is equivalent to INSERT IGNORE INTO. ON DUPLICATE KEY UPDATE overrides that behavior and performs an update instead of ignoring the conflict.

Insert a row with a timestamp column

Use NOW() to record the current timestamp at insert time.

INSERT INTO courses (name, submission_date)
VALUES ('Jams', NOW());

Insert rows from a query result

Use a SELECT statement as the data source. For syntax and examples, see INSERT SELECT FROM.

What's next

INSERT SELECT FROM — insert query results from another table
CREATE TABLE — define table schema, distribution keys, and partitions