Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Modify Data

  1. Home
  3. Exam 70-761: Querying Data with Transact-SQL
  5. Modify Data

Transact-SQL statements

It summarizes the categories of statements for use with Transact-SQL (T-SQL). You can find all of the statements listed in the left-hand navigation.

Backup and restore
The backup and restore statements provide ways to create backups and restore from backups. For more information, see the Backup and restore overview.

Data Definition Language
Data Definition Language (DDL) statements defines data structures. Use these statements to create, alter, or drop data structures in a database.

  • ALTER
  • Collations
  • CREATE
  • DROP
  • DISABLE TRIGGER
  • ENABLE TRIGGER
  • RENAME
  • UPDATE STATISTICS

Data Manipulation Language
Data Manipulation Language (DML) affect the information stored in the database. Use these statements to insert, update, and change the rows in the database.

  • BULK INSERT
  • DELETE
  • INSERT
  • UPDATE
  • MERGE
  • TRUNCATE TABLE

Permissions statements
Permissions statements determine which users and logins can access data and perform operations. For more information about authentication and access, see the Security center.

Service Broker statements
Service Broker is a feature that provides native support for messaging and queuing applications. For more information, see Service Broker.

Session settings
SET statements determine how the current session handles run time settings.

OUTPUT Clause (Transact-SQL)

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Note

An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

Used in:

  • DELETE
  • INSERT
  • UPDATE
  • MERGE

Syntax

<OUTPUT_Clause> : :=
{
[ OUTPUT INTO { @table_variable | output_table } [ ( column_list ) ] ]
[ OUTPUT ]
}
::=
{ | scalar_expression } [ [AS] column_alias_identifier ]
[ ,…n ]

::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
| $action

Arguments

@table_variable
Specifies a table variable that the returned rows are inserted into instead of being returned to the caller. @table_variable must be declared before the INSERT, UPDATE, DELETE, or MERGE statement.

If column_list is not specified, the table variable must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns, which must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

For more information about table variables, see table (Transact-SQL).

output_table
Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

If column_list is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

output_table cannot:

  • Have enabled triggers defined on it.
  • Participate on either side of a FOREIGN KEY constraint.
  • Have CHECK constraints or enabled rules.

column_list
Is an optional list of column names on the target table of the INTO clause. It is analogous to the column list allowed in the INSERTstatement.

scalar_expression
Is any combination of symbols and operators that evaluates to a single value. Aggregate functions are not permitted in scalar_expression.

Any reference to columns in the table being modified must be qualified with the INSERTED or DELETED prefix.

column_alias_identifier
Is an alternative name used to reference the column name.

DELETED
Is a column prefix that specifies the value deleted by the update or delete operation. Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed.

DELETED cannot be used with the OUTPUT clause in the INSERT statement.

INSERTED
Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

INSERTED cannot be used with the OUTPUT clause in the DELETE statement.

from_table_name
Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

*
Specifies that all columns affected by the delete, insert, or update action will be returned in the order in which they exist in the table.

For example, OUTPUT DELETED.* in the following DELETE statement returns all columns deleted from the ShoppingCartItem table:Copy

DELETE Sales.ShoppingCartItem  
    OUTPUT DELETED.*;

column_name
Is an explicit column reference. Any reference to the table being modified must be correctly qualified by either the INSERTED or the DELETED prefix as appropriate, for example: INSERTED**.**column_name.

$action
Is available only for the MERGE statement. Specifies a column of type nvarchar(10) in the OUTPUT clause in a MERGE statement that returns one of three values for each row: ‘INSERT’, ‘UPDATE’, or ‘DELETE’, according to the action that was performed on that row.

Remarks

The OUTPUT <dml_select_list> clause and the OUTPUT <dml_select_list> INTO { @table_variable | output_table } clause can be defined in a single INSERT, UPDATE, DELETE, or MERGE statement.

 Note: Unless specified otherwise, references to the OUTPUT clause refer to both the OUTPUT clause and the OUTPUT INTO clause.

The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.

When a computed column is included in the <dml_select_list>, the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed.

There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.

You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.

The OUTPUT clause is not supported in the following statements:

  • DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
  • INSERT statements that contain an EXECUTE statement.
  • Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
  • The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
  • A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

  • Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.
  • A column from a view or inline table-valued function when that column is defined by one of the following methods:
    • A subquery.
    • A user-defined function that performs user or system data access, or is assumed to perform such access.
    • A computed column that contains a user-defined function that performs user or system data access in its definition.
    When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised.

Inserting Data Returned From an OUTPUT Clause Into a Table

When you are capturing the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement and inserting those results into a target table, keep the following information in mind:

  • The whole operation is atomic. Either both the INSERT statement and the nested DML statement that contains the OUTPUT clause execute, or the whole statement fails.
  • The following restrictions apply to the target of the outer INSERT statement:
    • The target cannot be a remote table, view, or common table expression.
    • The target cannot have a FOREIGN KEY constraint, or be referenced by a FOREIGN KEY constraint.
    • Triggers cannot be defined on the target.
    • The target cannot participate in merge replication or updatable subscriptions for transactional replication.
  • The following restrictions apply to the nested DML statement:
    • The target cannot be a remote table or partitioned view.
    • The source itself cannot contain a <dml_table_source> clause.
  • The OUTPUT INTO clause is not supported in INSERT statements that contain a <dml_table_source> clause.
  • @@ROWCOUNT returns the rows inserted only by the outer INSERT statement.
  • @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.
  • Query notifications treat the statement as a single entity, and the type of any message that is created will be the type of the nested DML, even if the significant change is from the outer INSERT statement itself.
  • In the <dml_table_source> clause, the SELECT and WHERE clauses cannot include subqueries, aggregate functions, ranking functions, full-text predicates, user-defined functions that perform data access, or the TEXTPTR function.

Parallelism

An OUTPUT clause that returns results to the client will always use a serial plan.

In the context of a database set to compatibility level 130 or higher, if an INSERT…SELECT operation uses a WITH (TABLOCK) hint for the SELECT statement and also uses OUTPUT…INTO to insert into a temporary or user table, then the target table for the INSERT…SELECT will be eligible for parallelism depending on the subtree cost. The target table referenced in the OUTPUT INTO clause will not be eligible for parallelism.

Triggers

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation. If a statement that includes an OUTPUT clause is used inside the body of a trigger, table aliases must be used to reference the trigger inserted and deleted tables to avoid duplicating column references with the INSERTED and DELETED tables associated with OUTPUT.

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

If the sp_configure option disallow results from triggers is set, an OUTPUT clause without an INTO clause causes the statement to fail when it is invoked from within a trigger.

Data Types

The OUTPUT clause supports the large object data types: nvarchar(max), varchar(max), varbinary(max), text, ntext, image, and xml. When you use the .WRITE clause in the UPDATE statement to modify an nvarchar(max), varchar(max), or varbinary(max)column, the full before and after images of the values are returned if they are referenced. The TEXTPTR( ) function cannot appear as part of an expression on a text, ntext, or image column in the OUTPUT clause.

Queues

You can use OUTPUT in applications that use tables as queues, or to hold intermediate result sets. That is, the application is constantly adding or removing rows from the table. The following example uses the OUTPUT clause in a DELETE statement to return the deleted row to the calling application.

Menu