INSERT

Inserts new rows into the specified table or view that will contain either the explicitly specified values or the values returned by the query expression.

When you insert rows in a multi-tenant table, the data is inserted into the appropriate tenant partition. The INSERT statement is supplemented with a TENANT clause for SQL to understand which table partition receives the inserted row.

For a regular tenant in a multi-tenant table, the TENANT clause is optional. However, the tenant name must match the user's tenancy. Otherwise, the INSERT statement returns an error.

For a super-tenant or a DBA in a multi-tenant table, the TENANT clause must be the name of an existing tenant, or a default tenant. This conveys the information to the INSERT statement about the point of insertion of row(s) in an existing or default tenant partition of the multi-tenant table.

When the INSERT statement is applied to the group partition for a tenant, the TENANT clause names the tenant that is part of the multi-tenant table that is being inserted with rows. Any indexes for the table are updated using the partition information for the group.

You can also use the INSERT statement to insert rows into the specified partition of a partitioned table. You must have the write permissions on the partitioned table to add rows to it. Executing the INSERT statement to insert rows into a partitioned table may result in an error in the following cases:

• If all partitioned columns of the partitioned table are not specified in the INSERT statement or if they do not have any default values when the table is created.
• If the specified values of the partitioned columns do not determine in which partition the row should be inserted.
• If there is no space allocated in the partition to which the inserted row belongs.
• If the target partition is a read-only partition.

Syntax

INSERT INTO [owner_name] {table_name|view_name}
 [ TENANT tenant_name ] 
 [ ( column_name[, column_name] , ... ) ]
  [ ( column_name[, column_name] , ... ) ] 
    { VALUES ( value[, value] , ... ) |query_expression} ;

Notes

• If the optional list of column names is specified, then only the values for those columns are required. The rest of the columns of the inserted row will contain NULL values, provided that the table definition allows NULL values and there is no DEFAULT clause for the columns. If a DEFAULT clause is specified for a column and the column name is not present in the optional column list, then the column is given the default value.
• If the optional list is not specified, then the column values must be either explicitly specified or returned by the query expression. The order of the values should be the same as the order in which the columns are declared in the declaration of the table or view.
• The VALUES (...) form for specifying the column values inserts one row into the table. The query expression form inserts all the rows from the query results.

• A SELECT statement utilizing a NOLOCK hint can be used within an INSERT statement.

  For example:

  INSERT INTO PUB.CUSTOMER SELECT * FROM PUB.ARCHIVE_CUST WHERE ... WITH (NOLOCK);

  For more information using the NOLOCK hint in a SELECT statement, see .

• can be used if If the table contains a foreign key and there is no corresponding primary key that matches the values of the foreign key in the record being inserted, then the insert operation is rejected.

Examples

INSERT statement

The following provides examples of the INSERT statement:

INSERT INTO customer (cust_no, name, street, city, state)
     VALUES (1001, 'RALPH', '#10 Columbia Street', 'New York', 'NY') ;

INSERT INTO neworders (order_no, product, qty)
     SELECT order_no, product, qty
          FROM orders
          WHERE order_date = SYSDATE ;

Inserting a row in the tenant partition of a multi-tenant table

The example below directs the INSERT statement to insert a new row in the tenant partition for SNCSoftware in the multi-tenant table mtcustomer.

INSERT INTO pub.mtcustomer TENANT SNCSoftware
(custnum, name)
 VALUES (9999, 'West Side Sports');

Inserting rows from the default partition to other tenants

The example below lists two INSERT statements that move rows from the DEFAULT partition of mtcustomer, and then distribute the rows with an even customer number to the tenant SNCSoftware and rows with an odd customer number to the tenant OEDProducts.

INSERT INTO pub.mtcustomer TENANT SNCSoftware
 SELECT * 
  FROM pub.mtcustomer AS mtc
  WHERE tenantName_tbl (mtc) = 'default'
  AND MOD (custnum, 2) = 0;

INSERT INTO pub.mtcustomer TENANT OEDProducts
 SELECT *
  FROM pub.mtcustomer AS mtc
  WHERE tenantName_tbl (mtc) = 'default'
  AND MOD (custnum, 2) <> 0;

DELETE
 FROM pub.mtcustomer AS mtc
 WHERE tenantName_tbl (mtc) = 'default';

Inserting rows into a table partitioned by LIST

The examples below insert rows into the table Pub.tpcustomer that is partitioned by LIST.

Assume that the table is partitioned based on SalesRep as given below:

• PART1_LIST SalesRep IN ('SLS')
• PART2_LIST SalesRep IN ('JLP')
• PART3_LIST SalesRep IN ('KIK')
• PART4_LIST SalesRep IN ('BBB')

The below example inserts rows into the partition PART1_LIST:

INSERT INTO Pub.tpcustomer (cust_num, SalesRep) VALUES ( 100, 'SLS' );

The below example inserts rows into the partition PART4_LIST:

INSERT INTO Pub.tpcustomer (cust_num, SalesRep) VALUES ( 101, 'BBB' );

INSERT INTO Pub.tpcustomer (cust_num, SalesRep) VALUES ( 102, 'XYZ' );

Inserting rows into a table partitioned by RANGE

The examples below insert rows into the table Pub.tporder that is partitioned by RANGE.

Assume that the table is partitioned based on OrderDate as given below:

• PART1_RANGE OrderDate <= ('01/01/1998')
• PART2_RANGE OrderDate <= ('01/01/2010')
• PART3_RANGE OrderDate <= ('01/01/2018')

The below example inserts rows into the partition PART1_RANGE:

INSERT INTO Pub.tporder (ordernum, custnum, OrderDate) VALUES ( 1, 100, '10/10/1990' );

The below example inserts rows into the partition PART3_RANGE:

INSERT INTO Pub.tporder (ordernum, custnum, OrderDate) VALUES ( 2, 101, '10/10/2017' );

The below example inserts rows into the partition PART3_RANGE:

INSERT INTO Pub.tporder (ordernum, custnum, OrderDate) VALUES ( 2, 101, '01/01/2018' );

The below example returns an error since the row being inserted does not belong to any partition.

INSERT INTO Pub.tporder (ordernum, custnum, OrderDate) VALUES ( 2, 101, '01/01/2019' );