ColumnStore Insert

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.

How Autoincrement works

While the MariaDB ColumnStore's autoincrement column attribute behavior it is not identical to MariaDB's implementation. In order to get the correct results from this attribute please read and understand this section.

• Only a single column in a table can be defined as autoincrement.
• The column must be an integer type.
• Is inserted with an INSERT statement, a LOAD DATA INFILE statement, or cpimport and has a value coded, either explicitly or implicitly, as NULL, or is coded explicitly as 0 (zero).
• Is updated to the explicit values of NULL or 0 (zero). Note that if you update a column with the value of another column or with the result of an expression, the column will be updated to that value, regardless of whether the ultimate evaluation is NULL or 0 (zero).
• If you never manually update an autoincrement column, ColumnStore will always use a unique value.
• There is no guarantee that these values are consecutive or even increasing, only that they are unique MariaDB CoumnStore will assign this column a unique value for each row that:
• However, autoincrement columns are not constrained to unique values. If you insert or update a row and explicitly code a value [other than NULL or 0 (zero)], ColumnStore will insert that value (provided it meets all other requirements for that datatype), even if it causes duplicate values for the column, and no error or warning will be issued.
• If you change the table-level starting autoincrement value to a value higher than any value in the table, ColumnStore will begin using that new value on the next insert or update (and possibly introduce a gap in the numbers).
• If you change the value to a value lower than any value in the table, ColumnStore will still begin using that new value on subsequent inserts and updates, and thus duplicates will be created. No error or warning will be issued in either case.
• If the system is unable to generate sequence numbers because that will not fit into the destination datatype (e.g. if the autoincrement column is a TINYINT datatype and you attempt to insert more than about 127 rows), the statement or job will fail. At this point you must either reset the autoincrement settings or drop the autoincrement column and add a new autoincrement column with a wider datatype.
• LOAD DATA INFILE will yield warnings from MariaDB for NULL values in the source file on the autoincrement columns. To avoid these warnings, use 0 (zero) instead of NULL for the autoincrement column