insert

Syntax

insert [into] [database.[owner.]]{table_name|view_name}
	[(column_list)] 
	{values (expression [, expression]...)
		|select_statement [plan "abstract plan"]}

Parameters

• into – is optional.
• table_name | view_name – is the name of the table or view from which you want to remove rows. Specify the database name if the table or view is in another database, and specify the owner’s name if more than one table or view of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.
• column_list – is a list of one or more columns to which data is to be added. Enclose the list in parentheses. The columns can be listed in any order, but the incoming data (whether in a values clause or a select clause) must be in the same order. If a column has the IDENTITY property, you can substitute the syb_identity keyword for the actual column name.

  The column list is necessary when some, but not all, of the columns in the table are to receive data. If no column list is given, the SAP ASE server assumes that the insert affects all columns in the receiving table (in create table order).

• values – introduces a list of expressions.
• expression – specifies constant expressions, variables, parameters, or null values for the indicated columns. Enclose character and datetime constants in single or double quotes.

  You cannot use a subquery as an expression.

  The values list:

  • Must be enclosed in parentheses

  • Must match the explicit or implicit column list

  • Can use “default” as a value

  See System and User-Defined Datatypes in Reference Manual: Building Blocks for more information about data entry rules.

• select_statement – is a standard select statement used to retrieve the values to be inserted.
• plan "abstract plan" – specifies the abstract plan to use to optimize the query. It can be a full or partial plan, specified in the abstract plan language. Plans can only be specified for insert...select statements. See Creating and Using Abstract Plans in the Performance and Tuning Guide: Optimizer and Abstract Plans for more information.

Examples

• Example 1 –

  insert titles 
  values ("BU2222", "Faster!", "business", "1389", 
      null, null, null, "ok", "06/17/87", 0)

• Example 2 –

  insert titles
   (title_id, title, type, pub_id, notes, pubdate,
      contract) 
  values ('BU1237', 'Get Going!', 'business',
      '1389', 'great', '06/18/86', 1)

• Example 3 –

  insert newauthors 
      select * 
      from authors 
      where city = "San Francisco"

• Example 4 –

  insert test 
      select * 
      from test 
      where city = "San Francisco"

• Example 5 –

  insert table1 (col1, col2, col3, col4) 
      values (10, 4, default, 34)

Usage

• Use insert only to add new rows. Use update to modify column values in a row you have already inserted.

• When inserting into text, unitext, and image columns, an insert of a NULL into a text , ortext, or an image column simply allocates space for a text pointer. Use update to get a valid text pointer for that column.

• You can define a trigger that takes a specified action when an insert command is issued on a specified table.

• You can send an insert as a language event or as a parameterized dynamic statement to remote servers.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

The following are Transact-SQL extensions:

Permissions

The permission checks for insert differ based on your granular permissions settings.

Auditing

Values in event and extrainfo columns of sysaudits are: