INSERT Statement¶

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

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 { <view identifier> | <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.

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.

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.

This method can be used for three different features: - Loading of remote tables - Cache load - Load summaries

A view has to meet the following requirements to support this 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. Changes in the source table can include insertions or updates but not deletes. If rows are deleted in the source table, this command will not remove these data in the target table. In order to remove the data it is necessary to perform a full refresh of the table. 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');

Encode the binary data in hexadecimal. Inserting binary data works when the underlying database is one of these: Oracle, SQLServer, MySQL and PostgreSQL and Teradata.