The Ultimate Guide to High Availability with MariaDB

ColumnStore Insert

You are viewing an old version of this article. View the current version here.

Contents

  1. Syntax
    1. INSERT SELECT

The INSERT statement allows you to add data to tables.

Syntax

INSERT 
 INTO tbl_name [(col,...)]
 {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

The following statement inserts a row with all column values into the customer table:

INSERT INTO customer (custno, custname, custaddress, phoneno, cardnumber, comments) 
  VALUES (12, ‘JohnSmith’, ‘100 First Street, Dallas’, ‘(214) 555-1212’,100, ‘On Time’)

The following statement inserts two rows with all column values into the customer table:

INSERT INTO customer (custno, custname, custaddress, phoneno, cardnumber, comments) VALUES 
  (12, ‘JohnSmith’, ‘100 First Street, Dallas’, ‘(214) 555-1212’,100, ‘On Time’),
  (13, ‘John Q Public’, ‘200 Second Street, Dallas’, ‘(972) 555-1234’, 200, ‘LatePayment’);

INSERT SELECT

With INSERT ... SELECT, you can quickly insert many rows into a table from one or more other tables.

  • ColumnStore ignores the ON DUPLICATE KEY clause.
  • Non-transactional INSERT ... SELECT is directed to ColumnStores cpimport tool by default, which significantly increases performance.
  • Transactional INSERT ... SELECT statements (that is with AUTOCOMMIT off or after a START TRANSACTION) are processed through normal DML processes.
  • ColumnStore autoincrement columns will work as normal.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.