AnalyticDB:INSERT SELECT FROM

Usage notes

When you execute the INSERT INTO SELECT statement, AnalyticDB for MySQL cannot guarantee that the data can be written to the destination table in the same order as the results that are generated from the SELECT subquery.

Syntax

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

Parameters

• column_name: the name of the column. If you want to insert data of specific columns from the source table to the destination table, the columns specified in the SELECT subquery must use the same order and data types as the columns specified in the INSERT INTO statement.

• query: the SELECT subquery, such as SELECT FROM TABLE or SELECT FROM VIEW.

Examples

• Insert data of specific columns from the customer table to the new_customer table by specifying the column names.

  INSERT INTO new_customer (customer_id, customer_name, phone_num)
  SELECT customer_id, customer_name, phone_num FROM customer
  WHERE customer.customer_name ='Alan'; 

• Insert data of all columns from the customer table to the new_customer table without specifying column names.

  INSERT INTO new_customer
  SELECT customer_id,customer_name,phone_num,city_name,sex,id_number,home_address,office_address,age,login_time FROM customer
  WHERE customer.customer_name ='Alan';