How to Update Table View in SQL Server

Introduction 

In Enterprise applications, we find that there are numerous tables and those may be normalized. So data to be inserted for a "Business Entity" may be split into various tables as part of normailzation. So to ensure that you include related tables into your select queries, views are a good medium.

What are views

Views are nothing but a virtual table (note that a table is not created for a view). The view will only have a SQL statement that will be executed in background once you query the view. It will have a similar structure like a table (rows, columns and so on).

The following is the syntax for creating views (in its simplest form):

CREATE VIEW [ schema. ] view_name
AS select_statement

Schema: Is the name of the schema to which the view belongs.

View_name: Is the name of the view.

AS: Specifies the actions the view is to perform.

Select_statement: Is the SELECT statement that defines the view. The statement can use more than one table and other views.

Updatable Views

Once you have created a view, we can use that view to update data in the underlying tables. Since views are just SQL select statements, there are a few restrictions on which views can update data back to the tables. The most logical that I can think of is that if you are projecting the result of some computation over a table column, how is the view supposed to revert that computation when trying to insert data back to the table? The complete list of restrictions are given below (reference: MSDN).

• Any modifications, including UPDATE, INSERT and DELETE statements, must reference columns from only one base table.
• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following.

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

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

• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.