Adds new rows to a table by directly specifying the row data to be inserted (valued form) or by retrieving the new row data from another table (selected, or INSERT ... SELECT form).
The following syntax describes the valued and selected forms of the statement, where subquery represents the SELECT portion of the selected form.
For more information, see:
- Scalar Subqueries in SELECT Statements
- MERGE
- CREATE ERROR TABLE
- Inserting Values into a JSON Column
- Teradata Vantage™ - Geospatial Data Types, B035-1181
- Using INSERT ... SELECT to Populate New Table
- Teradata® FastLoad Reference, B035-2411
- Teradata® MultiLoad Reference, B035-2409
- Teradata® Parallel Data Pump Reference, B035-3021
ANSI Compliance
INSERT is ANSI SQL:2011-compliant with extensions.
The INS abbreviation is a Teradata extension to the ANSI SQL:2011 standard.
In the ANSI SQL:2011 definition, this statement is named INSERT INTO, while in the Teradata definition, INTO is an optional keyword.
Required Privileges
The following privilege rules apply to the INSERT statement:
- You must have the INSERT privilege on the referenced table or column set.
- To insert rows into a table through a view, you must have the INSERT privilege on the view. Also, the immediate owner of the view must have the INSERT privilege on the underlying view, base table, or column set. The immediate owner of the view is the database in which the view resides.
- To insert rows into a table using a query specification, you must have the SELECT privilege for the referenced tables or views.
- When you perform an INSERT with a WHERE clause predicate that requires READ access to an object, you must have the SELECT privilege on the accessed object.
The privileges required for an INSERT ... SELECT ... LOGGING ERRORS operation are the same as those for INSERT ... SELECT operations without a LOGGING ERRORS option except that you must also have the INSERT privilege on the error table associated with the target data table for the INSERT ... SELECT operation.