INSERT
The INSERT statement adds rows to a table. You can insert a single row or multiple rows with one statement.
You can use an INSERT statement only in a SQL script or from a JDBC/ODBC call. See also
INSERT, UPDATE, and DELETE on Views.
The INSERT INTO statement can also be used to insert a complete row of values without specifying the column names. Values must be specified for every column in the table, in the order specified by the DDL. If the number of values is not the same as the number of columns in the table, or if a value is not allowed for a particular data type, an exception is thrown.
The INSERT statement itself does not return a result, but the database system returns a message indicating how many rows have been affected. You can then verify the insertion by querying the data source.
Warning: If a network connection is dropped while data is being moved through TDV using INSERT statements, queries are likely to fail. The TDV Server cannot reconcile the data when the connection is re-established. You will need to determine when the failure occurred, how much data might have moved, and the best way to resolve the failure.
TDV supports INSERT only for the following data sources.
• TDV | • Oracle |
• DataDirect—Mainframe | • PostgreSQL |
• File—Delimited | • REST |
• Informix | • SOAP |
• Microsoft Access (Windows platform only) | • Sybase ASE |
• Microsoft Excel | • Sybase IQ |
• Microsoft SQL Server | • Teradata |
• MySQL | • |
• Netezza | |
Note: For add-ons such as adapters, consult the documentation to find out if INSERT is supported.
Three forms of INSERT syntax are supported for TDV as a data source.
Syntax 1
INSERT INTO <table_name> DEFAULT VALUES
Syntax 2
INSERT INTO <table_name> [(<columnA, ... columnX>)]
VALUES (<valueList>)[,(<valueList>)]*
Syntax 3
INSERT INTO <table_name> [(<columnA, ... columnX>)]
<queryExpression>
Opening and closing parentheses are used for grouping; <queryExpression> indicates a SELECT statement.
Listing of the columns is optional. In all cases, the number and type of the values must be equal and consistent with the number of columns in the row or as specified. See
Example (Multi-Row INSERT with <queryExpression>).
Remarks
• The system automatically discards any ORDER BY in the subqueries, because it is not useful to sort the subquery.
• In a multi-row INSERT, the query result must contain the same number of columns in the same order as the column list in the INSERT statement, and the data types must be compatible, column by column.
• If a non-nullable column is set to NULL, the data source throws a runtime exception.
• INSERT statements should include all non-nullable columns.
• Derived columns cannot be present in an INSERT statement.
Example (Single-Row INSERT)
PROCEDURE sc2()
BEGIN
INSERT INTO
/shared/examples/ds_inventory/products (ProductID, ProductName, UnitPrice)
VALUES (23, 'monitor', 500.00);
END
Example (Multi-Row INSERT)
PROCEDURE sc2()
BEGIN
INSERT INTO
/shared/examples/ds_inventory/products (ProductID, ProductName,
UnitPrice)
VALUES
(41, 'monitor', 1000/10 * 1),
(42, 'monitor', 1000/10 * 1),
(43, 'monitor', 1000/10 * 1);
END
Example (Multi-Row INSERT with <queryExpression>)
PROCEDURE get_open_orders(OUT numOpen INTEGER)
BEGIN
-- Clear the table
DELETE FROM /users/composite/test/sources/mysql/updates;
-- Get all open orders
INSERT INTO /users/composite/test/sources/mysql/updates
(c_bigint, c_varchar)
SELECT OrderID, Status
FROM /shared/tutorial/sources/ds_orders/orderdetails
WHERE Status = 'Open';
-- Return number of open orders
SELECT count(*) INTO numOpen
FROM /users/composite/test/sources/mysql/updates;
END
Example (INSERT with DEFAULT)
INSERT INTO Customers (FirstName, LastName, Country)
VALUES ('joe','Ely', DEFAULT)
An exception is thrown if the target database does not support the DEFAULT keyword.
A runtime exception is thrown if the column does not have a default defined and is non-nullable.
Example (INSERT with DEFAULT VALUES)
INSERT INTO Customers DEFAULT VALUES
If a DEFAULT VALUES clause is specified, a single row is inserted into a table containing the appropriate defaults (possibly null) in every column. It is an error if any column has no default.