INSERT¶

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

The INSERT statement inserts rows into a table.

This statement has the following format:

[REPEATED]]INSERT INTO [schema.]table_name

    [(column {, column})]

    [OVERRIDING SYSTEM VALUE | OVERRIDING USER VALUE]

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

    [WITH common_table_expression] 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, Actian Data Platform places the result of the first exprin 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.

OVERRIDING SYSTEM VALUE

Overrides the sequence value for a GENERATED ALWAYS AS IDENTITY column with the explicit value specified in the VALUES clause. Cannot be used on an INSERT...SELECT statement.

OVERRIDING USER VALUE

Overrides the sequence value for a GENERATED BY DEFAULT IDENTITY column with the explicit value specified in the VALUES clause. Cannot be used on an INSERT...SELECT statement.

See CREATE TABLE.

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.

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

If a column corresponding to an expris the identity column and there is no OVERRIDING clause, the value must be DEFAULT.

WITH common_table_expression

Defines a common table expression (see WITH (common_table_expression)).

INSERT Examples¶

When inserting values into a Actian Data Platform table, always insert many rows at a time rather than performing individual inserts. Consider using a staging table when single row inserts cannot be avoided.

CREATE TABLE employee_dim(

    employee_id INTEGER,

    emp_name VARCHAR(20),

    emp_rating DECIMAL(1,0),

    salary MONEY,

    manager VARCHAR(20),

    hiredate ANSIDATE,

    dept_no VARCHAR(12));

INSERT INTO employee_dim VALUES

(1,'Mike Sannicandro', 1,40000.00,'Al Obidinski','2013-10-22','Pers'),

(2,'Dave Murtagh', 4,20000.00,'Al Obidinski','2014-03-19',NULL),

(3,'Benny Barth', 9,5000.00,'Al Obidinski','2003-03-03','Stores'),

(9,'Al Obidinski', 7,50000.00,NULL,'1998-04-24','Dev'),

(5,'Dean Reilly', 4,50300.00,'Al Obidinski','2003-06-10','Dev');

DECLARE GLOBAL TEMPORARY TABLE YYMD00(

        rating DECIMAL(1),

        name VARCHAR(20),

        id BIGINT) ON COMMIT PRESERVE ROWS WITH NORECOVERY;

INSERT INTO YYMD00 (rating,name) VALUES (1,'Fred'),(2,'Toby'),(3,'Mary');

INSERT INTO YYMD00 SELECT emp_rating, emp_name, employee_id

   FROM employee_dim WHERE emp_rating = 2;

INSERT INTO YYMD00 WITH emp_cte AS (SELECT emp.emp_rating, emp.emp_name, emp.employee_id FROM employee_dim emp WHERE emp_rating = 3) SELECT * FROM emp_cte;

SELECT name FROM YYMD00 WHERE rating = 2;