PUT INTO

Creates or replaces rows in a table without first checking existing primary key values. If existing rows with the same primary key are present in the table, they are overwritten with the new values. This syntax is used to speed insert operations, especially with HDFS-persistent tables where primary key checks may need to retrieve data from HDFS.

Syntax

PUT INTO table-name
    { VALUES ( column-value [ , column-value ]* ) |
   Query
    }

Description

Note: GemFire XD does not support a PUT INTO with a subselect query if any subselect query requires aggregation.
Note: GemFire XD does not support using triggers in combination with the PUT INTO DML syntax. A PUT INTO operation does not invoke triggers. In addition, you cannot use PUT INTO in the triggered SQL statement of a GemFire XD trigger. See CREATE TRIGGER.

PUT INTO speeds insert operations on HDFS read/write tables by ignoring existing key values. PUT INTO uses a syntax similar to the INSERT statement, but GemFire XD does not check existing primary key values before executing the PUT INTO command. If a row with the same key exists in the table, PUT INTO simply overwrites the older row value. If no rows with the same primary key exist, PUT INTO operates like a standard INSERT. Removing the primary key check speeds execution when updating large numbers of rows in HDFS stores.

GemFire XD honors all other column constraints (unique, check, and foreign key) when executing a PUT INTO statement, unless you choose to import data using the skip-constraint-checks connection property.

Example

-- Insert a new row, or update an existing row with the provided values (ignoring any primary key constraints).
PUT INTO TRADE.HDFSCUSTOMERS
      VALUES (1, 'J Pearson', '07-06-2002', 'VMWare', 1);

-- Insert using a select statement, overwriting rows if necessary.
PUT INTO TRADE.HDFSNEWCUSTOMERS
     SELECT * from TRADE.CUSTOMERS WHERE TID=1;