From Ingres Community Wiki

Overview

Ingres SQL INSERT implements the SQL2003 standard feature level E101-01 of only supporting the insertion of one row at a time.

Allowing multiple rows to be inserted as part of the same statement is defined in the standard in feature set F641. Not only is this a convenience feature, it also provides efficencies akin to bulk loading through SQL.

Syntax Changes

• <insert statement> ::= INSERT INTO <insert columns and source>
  • <insert columns and source> ::= <from subquery> | <from constructor> | <from default>
    • <from subquery> ::= [ '(' <column name list> ')' ] [ <override clause> ] <query expression>
    • <from constructor> ::= [ '(' <column name list> ')' ] [ <override clause> ] <contextually typed table value constructor>
      • <contextually typed table value constructor> ::= VALUES <contextually typed row value expression list>
        • <contextually typed row value expression list> ::= <contextually typed row value expression> [ { <comma> <contextually typed row value expression> }... ]
    • <from default> ::= DEFAULT VALUES
  • <column name list> ::= <column name> [ { ',' <column name> }... ]
  • <override clause> ::= OVERRIDING USER VALUE | OVERRIDING SYSTEM VALUE
  [edit]

  Implementation Levels

  Feature set F641 - Row and Table Constructors.

  [edit]

  Scope

  [edit]

  ADF Changes

  • None needed.
  [edit]

  PSF Changes

  • The parser will need extending to handle the productions required for the additional syntax.
  [edit]

  OPF Changes

  • None needed.
  [edit]

  QEF Changes

  None needed.

  [edit]

  SCF Changes

  None needed.

  [edit]

  Front End

  • None needed.
  [edit]

  Ingres Update

  [edit]

  Change Info

  Bug# : 124599

  IP : http://reviewboard.ingres.prv/r/9485/

  SVN Rev : 4347

  [edit]

  QA

  [edit]

  Documentation

  [edit]

  INSERT

  Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET

  The INSERT statement inserts rows into a table. This statement has the following format:

  [EXEC SQL [REPEATED]]INSERT INTO [schema.]table_name 
                [(column {, column})]
                [VALUES (expr{, expr}) {,(expr{, expr} ) } | subselect];
  

  REPEATED

  Saves the execution plan of the insert, which can make subsequent executions faster.

  column {,column}

  Identifies the columns of the specified table into which the values are placed. When the column list is included, the DBMS Server places the result of the first expr in the values list or subselect into the first column named, the second value into the second column named, and so on. The data types of the values must be compatible with the data types of the columns in which they are placed.

  VALUES (expr{ ,expr}) {,(expr{ ,expr})} | subselect

  Specifies the values to be inserted as one of the following:

  • One or more comma separated sets of lists of expressions, each representing one row of values for insertion. The expressions in each row must correspond with the column list specified.

  INSERT INTO tbl (col1,col2) VALUES (1,’a’), (2,’b’), (3,‘c’);
  

  • A subselect, which inserts all the rows that result from the evaluation of the subselect. For the syntax of subselect, see Select (interactive) (see page 714).

  INSERT INTO tbl (col1,col2) SELECT a, b FROM tbl2;
  

  If a column corresponding to a value is the identity column and there is no OVERRIDING clause, the value must be DEFAULT.
  Retrieved from "http://community.actian.com/wiki/Multi-row_INSERT"

  Categories: Projects | DBMS Projects | DBMS