INSERT Statement

The INSERT statement inserts one or more tuples in a view, updating the underlying data source.

Syntax of the INSERT statement
INSERT INTO <view identifier> ( <field name> [, <field name> ]* )
    VALUES ( <value> [, <value> ]* )
    [ RETURNING <field name> [ , <field name> ]* ]
    [ CONTEXT ( <context information> [, <context information> ]* ) ]
    [ TRACE ]

INSERT INTO <view identifier>
    VALUES ( <value> [, <value> ]* )
    [ RETURNING <field name> [, <field name> ]* ]
    [ CONTEXT ( <context information> [, <context information> ]* ) ]
    [ TRACE ]

INSERT INTO <view identifier> [ ( <field name> [, <field name> ]* ) ]
    [ RETURNING <field name> [, <field name>]* ]
    VALUES ( <value> [, <value> ]* ) [, ( <value> [, <value> ]* ) ]*
    [ TRACE ]

INSERT INTO <view identifier>
    SET <field name> = <value> [, <field name> = <value> ]*
    [ RETURNING <field name> [, <field name>]* ]
    [ CONTEXT ( <context information> [, <context information> ]* ) ]
    [ TRACE ]

INSERT INTO { <identifier of a base view of a remote table> | <materialized table identifier> |
              <temporary table identifier> }
     { ON DUPLICATE KEY UPDATE |
       ON DUPLICATE KEY ( <field name> [, <field name>]* ) UPDATE }
     [ OPTIONS ( <option information> [ , <option information> ]* ) ]
     <select query>
     [ CONTEXT ( <context information> [, <context information> ]* ) ]
     [ TRACE ]

<field name> ::=
  <identifier>[.<identifier>]

<value> ::=
    NULL
  | <number>
  | <boolean>
  | <literal>
  | <function>

<view identifier> ::= (see Basic elements of VQL statements)

<identifier of a base view of a remote table> ::= Base view created with the CREATE_REMOTE_TABLE procedure or with the Create Remote Table form.

<materialized table identifier> ::= (see Materialized Tables)

<temporary table identifier> ::= (see Temporary Tables)

<option information> := Optional parameters to configure the insertion. See CREATE REMOTE TABLE Command to get information about the options available.

<select query> ::= (see Syntax of the SELECT statement)
The section Return Modified Rows explains how the RETURNING clause works.

INSERT INTO view <SELECT QUERY>

This command does the following steps:

  • If the select query is delegable to the data source where the target view is (direct load is possible) then VDP executes a INSERT INTO table <SELECT QUERY> command in the target data source.

  • When the select query is not delegable then:

    1. Executes the select query in the VDP Server.

    2. Inserts into the view the rows obtained in the execution of the query of the previous step.

This type of INSERT requires that the data source supports data movement optimization. Some data sources require to enable the Bulk Data Load for support data movements.

The following privileges are necessary to execute this command:

  • Insert over the view.

  • Execute over the views referenced in the select query.

INSERT INTO view ON DUPLICATE KEY UPDATE <SELECT QUERY>

This command does the following steps:

  • If the select query is delegable to the data source where the target view is (direct load is possible) then VDP executes a MERGE INTO table <SELECT QUERY> command in the target data source. This command merge the result of the select query into the target table.

  • When the select query is not delegable then:

    1. Creates a temporary table in the cache data source and inserts the result of the select query.

    2. Merges the temporary table into the target table, also known as UPSERT.

    3. Drops the temporary table.

A view has to meet the following requirements to support this cache load method:

  1. The target view must have a primary key.

  2. The view has to have one or more columns that indicate when the row was added or updated.

  3. Virtual DataPort must support pushing down to the source conditions over these fields that indicate when each row was added/updated.

  4. The source cannot remove rows from the data; only add or update them. If rows are deleted in the source, the view will return the deleted rows until the cache of this view is invalidated and cached again. Many scenarios meet this requirement because rows are not actually deleted but instead, are marked as deleted or invalid.

See the section Data Sources That Support Merge Data to get the list of data sources that support this command.

The following requirements are necessary to execute this command:

  • MySQL and PostgreSQL: the keys must be the primary key of the view or an unique index.

  • MySQL: VDP will show an error if there are multiple unique restrictions in the table. MySQL recommends avoiding the use of the ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes. More info.

  • Teradata: The target table where VDP is going to insert the data must have a Primary Index, otherwise the INSERT command will fail. The CREATE_REMOTE_TABLE stored procedure creates a Primary Index in Teradata when it is possible. Read the stored procedure documentation for more information. If you try to execute an INSERT INTO view ON DUPLICATE KEY UPDATE command on a table created without Primary Index you will get the following error:

An invalid statement was attempted on a table without a primary index: Merge-Into NoPI tables is not allowed.

Examples:

Add a row to the view internet_inc

INSERT INTO internet_inc (iinc_id, summary, taxid, specific_field1, specific_field2)
VALUES (6, 'Error in ADSL Router', 'B78596015', '5', '6')

A row will be added in the source database to the table associated with the internet_inc view.

Alternative syntax to add a row to the view internet_inc

INSERT INTO internet_inc
SET
    iinc_id = 6
  , summary = 'Error in ADSL router'
  , taxid = 'B78596015'
  , specific_field1 = '5'
  , specific_field2 = '6'

Add several rows to a view

INSERT INTO employee (last_name, first_name, title) VALUES
    ('Callahan', 'Laura', 'IT Staff')
  , ('Edwards', 'Nancy', 'Sales Manager')
  , ('King', 'Robert', 'IT Staff');

Insert using select query

INSERT INTO all_sales
SELECT * FROM online_sales
WHERE year = getyear(CURRENT_DATE());

Insert and update using select query

INSERT INTO all_sales ON DUPLICATE KEY(sale_id) UPDATE
SELECT * FROM online_sales
WHERE modification_date > '@LAST_REFRESH_DATE';

This command inserts and updates the new and modified online sales since the last insertion into the all_sales view. The field modification_date contains the timestamp when a row was inserted or modified. During the query execution, the variable @LAST_REFRESH_DATE is replaced with the timestamp of the last time a query to insert data in the view all_sales was executed and finished successfully. The command uses the sale_id field to determine if a row is already cached. A row from the cache and a row from the source are considered the same if the value of this field is the same. If the command does not explicitly set the comparison field then it will use the primary key of the view.

Insert binary data

INSERT INTO files(file) values ('FFD8FFDB00430003020202020203020202030303030406040404040408060605060
9080A0A090809090A0C0F0C0A0B0E0B09090D110D0E0F101011100A0C12131210130F101010FFC9000B080001000101011100FFCC000
600101005FFDA0008010100003F00D2CF20FFD9');

Binary data should be encoded in Hexadecimal code. This operation works in Oracle, SQLServer, MySQL and PostgreSQL and Teradata.