AnalyticDB：INSERT INTO

Sintaks

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

Parameter

• IGNORE: Mengabaikan penyisipan baris dengan nilai kunci primer duplikat. Parameter ini opsional.

• column_name: Nama kolom. Parameter ini opsional.

• query: Pernyataan SELECT yang hasil kuerinya dapat disisipkan ke dalam tabel.

Catatan penggunaan

Jika Anda tidak menentukan nama kolom, kolom dalam data yang akan disisipkan harus diatur sesuai urutan kolom yang ditentukan dalam pernyataan CREATE TABLE.

Contoh

Buat tabel bernama customer dan courses.

CREATE TABLE customer (
  customer_id bigint NOT NULL COMMENT 'Customer ID',
  customer_name varchar NOT NULL COMMENT 'Nama pelanggan',
  phone_num bigint NOT NULL COMMENT 'Nomor telepon',
  city_name varchar NOT NULL COMMENT 'Kota',
  sex int NOT NULL COMMENT 'Jenis kelamin',
  id_number varchar NOT NULL COMMENT 'Nomor kartu identitas',
  home_address varchar NOT NULL COMMENT 'Alamat rumah',
  office_address varchar NOT NULL COMMENT 'Alamat kantor',
  age int NOT NULL COMMENT 'Usia',
  login_time timestamp NOT NULL COMMENT 'Waktu masuk',
  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 'Tabel informasi pelanggan';           

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)          

• Sisipkan satu baris data ke dalam tabel customer.

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

• Jalankan pernyataan INSERT INTO ... ON DUPLICATE KEY UPDATE... untuk menyisipkan satu baris data ke dalam tabel customer.

  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;

• Sisipkan beberapa baris data ke dalam tabel customer.

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

• Sisipkan beberapa catatan ke tabel customer tanpa menentukan nama kolom.

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

• Sisipkan satu baris data ke dalam tabel courses.

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

• Untuk informasi tentang contoh INSERT query, lihat INSERT SELECT FROM.