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> }
     [ 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 primitives for specifying 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:

  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, or Connect and Write over the database where is the view.

  • Execute over the views referenced in the select query.

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());