Inserting into Row-Partitioned Tables
- Inserting rows into empty row partitions is optimized to avoid transient journaling of each row inserted into those partitions. This optimization can only be applied to tables that are not defined with referential integrity relationships.
- The outcome of partitioning expression evaluation errors, such as divide-by-zero errors, depends on the session mode.
Session Modework unit Work Unit that Expression Evaluation Errors Roll Back ANSI Request that contains the aborted request. Teradata Transaction that contains the aborted request. - When inserting rows into a single-level row-partitioned table for which the partitioning expression is not a RANGE_N or CASE_N function, the partition expression must result in a nonnull value between 1 and 65,535 after casting to INTEGER, if the data type is not INTEGER.
- When inserting rows into a row-partitioned table, any of the partitioning expressions (which must be RANGE_N or CASE_N functions) for that row must result in a nonnull value.
- When inserting rows into a base table that cause an insert into a row-partitioned join index, any of the partitioning expressions for that join index must result in a nonnull value.
- When inserting rows into a base table that cause an insert into a single-level row-partitioned join index, for a partitioning expression that is not RANGE_N or CASE_N, the result of the partitioning expression must be a nonnull value between 1 and 65,535 after casting to INTEGER, if the data type is not INTEGER.
- When inserting rows into a base table that cause an update of a join index row in a row-partitioned join index, any of the partitioning expressions for that updated index row must result in a nonnull value.
- When inserting rows into a base table that cause an update of a join index row in a row-partitioned join index with single-level row partitioning where the partitioning expression is not RANGE_N or CASE_N, the result of the partitioning expression must be a nonnull value between 1 and 65,535 after casting to INTEGER, if the data type is not INTEGER.
- You cannot assign either a value or a null to the system-derived columns PARTITION or PARTITION#L1 through PARTITION#L62 in an insert operation.
- The session mode and session collation at the time the row-partitioned table was created does not have to match the current session mode and collation for the insert operation to succeed. This is because the partition a row is to be inserted into is determined by evaluating the partitioning expression on partitioning column values using the table’s session mode and collation.
- Collation has the following implications for inserting rows into tables defined with a character partitioning:
- If the collation for a row-partitioned table is either MULTINATIONAL or CHARSET_COLL, and the definition for the collation has changed since the table was created, Teradata Database aborts any request that attempts to insert a row into the table and returns an error to the requestor.
- If a noncompressed join index with character partitioning under either an MULTINATIONAL or CHARSET_COLL collation sequence is defined on a table, and the definition for the collation has changed since the join index was created, the system aborts any request that attempts to insert a row into the table and returns an error to the requestor whether the insert would have resulted in rows being modified in the join index or not.
- If a partitioning expression for a table or noncompressed join index involves Unicode character expressions or literals, and the system has been backed down to a release that has Unicode code points that do not match the code points that were in effect when the table or join index was defined, Teradata Database aborts any attempts to insert rows into the table and returns an error to the requestor.
Inserting into Global Temporary Tables
Inserting into a global temporary table creates an instance of that table in the current session.
Inserting into NoPI Tables
You can use INSERT to insert rows into NoPI tables. Teradata Parallel Data Pump operations use Array INSERT on NoPI tables to load data. You can also perform batch inserts of data into NoPI tables using INSERT … SELECT operations. Inserts are transient journaled in the same way they are for primary indexed tables. For more information about NoPI tables, see Teradata Vantage™ - Database Design, B035-1094.
The following INSERT examples are based on these table definitions:
CREATE TABLE sales, FALLBACK ( ItemNbr INTEGER NOT NULL, SaleDate DATE FORMAT 'MM/DD/YYYY' NOT NULL, ItemCount INTEGER) PRIMARY INDEX (ItemNbr); CREATE TABLE newsales, FALLBACK ( ItemNbr INTEGER NOT NULL, SaleDate DATE FORMAT 'MM/DD/YYYY' NOT NULL, ItemCount INTEGER) NO PRIMARY INDEX;
For INSERT requests, the system randomly selects an AMP to send the row or rows. This is true for simple requests like the one below, and more complex requests that involve the insertion of multiple arrays of rows.
INSERT INTO newsales (100, '11/01/2007', 10);
The AMP then converts the row or array of rows into the proper internal format and appends them to the end of the target table (newsales in this example).
When inserting data from a source table into a NoPI target table using an INSERT … SELECT request like the one below, data from the source table is not redistributed. The data is locally appended to the target table.
If the SELECT is constrained from a source table, and the rows returned from the AMPs are skewed, the NoPI or NoPI column-partitioned table can become skewed, because the set of rows is locally inserted into the table without redistribution.
INSERT INTO newsales SELECT * FROM sales;