INSERT/INSERT SELECT Statement | SQL Statements | Teradata Vantage - 17.05 - INSERT/INSERT … SELECT - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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 syntax below describes the valued and selected forms of the statement, where subquery represents the SELECT portion of the selected form.

For information about syntax that is compatible with temporal tables, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .

For more information, see:
  • Scalar Subqueries
  • MERGE
  • Teradata Vantage™ - SQL Date and Time Functions and Expressions, B035-1211
  • CREATE ERROR TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144
  • Teradata Vantage™ - ANSI Temporal Table Support , B035-1186
  • Teradata Vantage™ - Temporal Table Support , B035-1182
  • Teradata Vantage™ - JSON Data Type, B035-1150
  • Teradata Vantage™ - Geospatial Data Types , B035-1181
  • Teradata Vantage™ - Database Administration, B035-1093
  • Teradata Vantage™ - Database Utilities , B035-1102
  • 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.