Modifying Data Through a View

Syntax:

1. Insert data using a view:
   INSERT [ INTO ] 
        view_name
        [ ( column1, ... ) ] 
           VALUES ( value1, ... )
   
2. Update data using a view:
   UPDATE view_name
     SET column1=value1, ...
       [ WHERE condition ] 
   
3. Remove Data using a view:
   DELETE 
      [ FROM ] 
         view_name
      [ WHERE condition ]
   
4. To get information about a view:
   sp_helptext view_name
   
5. Dependencies of a view:
   sp_depends view_name

INTO

An optional keyword used between the INSERT keyword and the name of the view.

view_name

The name of the view.

(column1, ...)

The list of columns into which values are to be inserted or updated.

VALUES

The keyword used just before the list of values.

(value1, ...)

The values that are to be inserted or updated, in the columns mentioned.

SET

The keyword which specifies the list of columns to be updated.

WHERE

The keyword that specifies the conditions that limit the rows to be updated.

condition

The condition that determines which rows are to be updated or deleted.

Views can be used for data modification:

• If the view contains at least one table in the FROM clause of the view definition.
• If no aggregate functions (such as MIN, MAX, AVG, COUNT, SUM, etc.), GROUP BY, UNION, DISTINCT, or TOP clauses are used in the main query. Aggregate functions, however, can be used in a subquery.
• The view has no derived columns (columns derived using operators and functions) in the result set.