USER MANUALS

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

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.

Add feedback