Use the INSERT statement to add new rows to a table.
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.
The following example depicts a single row being added to a table.
INSERT INTO Customer (CustNum, Name, Address, City, State)
VALUES
(1001, 'Global Fitness', '10 Columbia Street', 'New York', 'NY') ;
INSERT statements can also be executed based upon the results of a query expression. In the following example, rows are created in a table of New York customers based on a query of the Customer table.
INSERT INTO NYCustomer (CustNum, Name)
SELECT CustNum, Name FROM
Customer WHERE state = 'NY';
The following example 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');
The following example 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';
Note: If the optional column list is used, then only the values for those columns in the statement are required. Otherwise, values must be specified or returned by a query expression. Using VALUES to specify columns will insert one row into the table. Use the query expression to insert multiple rows.
The following examples direct the INSERT statement to 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')
INSERT INTO Pub.tpcustomer (cust_num, SalesRep) VALUES ( 100, 'SLS' );
The above statement inserts rows into the partition PART1_LIST.
INSERT INTO Pub.tpcustomer (cust_num, SalesRep) VALUES ( 101, 'BBB' );
The above statement inserts rows into the partition PART4_LIST.
For more information on the INSERT statement, see OpenEdge Data Management: SQL Reference.
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.