Modifying Data Through a View

You can modify the data of an underlying base table through a view, in the same manner as you modify data in a table by using UPDATE, INSERT and DELETE statements or by using the bcp utility and BULK INSERT statement. However, the following restrictions apply to updating views, but do not apply to tables:

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

• The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:

  • An aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP).

  • A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) amount to a computation and are also not updatable.

• The columns that are being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

• TOP cannot be used anywhere in the select_statement of the view when WITH CHECK OPTION is also specified.

The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. In general, SQL Server 2008 must be able to unambiguously trace modifications from the view definition to one base table. For example, the following view is not updatable:

CREATE VIEW TotalSalesContacts
AS
SELECT C.LastName, 
SUM(O.TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader O, Person.Contact C
WHERE C.ContactID = O.ContactID
GROUP BY LastName

A modification made to the LastName column of TotalSalesContacts would be unacceptable, because the column has been affected by a GROUP BY clause. If there is more than one instance of the same last name, SQL Server would not know which one to update, insert, or delete. Likewise, an attempt instead to modify the TotalSales column of TotalSalesContacts would return an error, because it is a column that is derived from an aggregate function. SQL Server cannot trace this column directly to its base table, SalesOrderHeader.

The following additional guidelines apply:

• All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.

• INSERT statements must specify values for any columns in the underlying table that do not allow null values and have no DEFAULT definitions.

• The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.

• A distributed partition view (remote view) cannot be updated using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.

• Bulk importing data into a partitioned view is unsupported by bcp or the BULK INSERT and INSERT ... SELECT * FROM OPENROWSET(BULK...) statements. However, you can insert multiple rows into a partitioned view by using the INSERT statement. For more information, see Bulk Exporting Data from or Bulk Importing Data to a View.

• The READTEXT and WRITETEXT statements cannot be used with text, ntext, or image columns in a view.