T-SQL » Views » 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.

Examples

Code:
INSERT INTO vwStudentDetails1(Id, Name, Std_Course, Phone, Std_Grade)
VALUES (20, 'Sam Simon', 4, 98758, 2);
Output:
(1 row(s) affected)
Explanation:

Here a new row with Id=20 is inserted into the 'Students' table. A SELECT query can verify that the new row was added.

Language(s): MS SQL Server
Code:
UPDATE vwStudentDetails1
SET Name='Billy James', Phone=27751
WHERE Id=20;


SELECT * FROM Students WHERE Id=20;
Output:
(1 row(s) affected)


IdNameStd_CoursePhoneStd_Grade
20Billy James4277512





(1 row(s) affected)
Explanation:

In this example, the first statement updates the data, while the second statement is used to verify the results.

Language(s): MS SQL Server
Code:
DELETE FROM vwStudentDetails WHERE Id=20;
Output:
(1 row(s) affected)
Explanation:

Here the row with Id=20 gets deleted from the 'Students' table. It can be verified by querying the table.

Language(s): MS SQL Server
Code:
sp_helptext vwStudentDetails;
Output:
CREATE VIEW vwStudentDetails
AS
SELECT * FROM Students;
Explanation:

The above is the definition of the view, vwStudentDetails.

Language(s): MS SQL Server
Code:
sp_depends vwStudentDetails;
Output:
In the current database, the specified object references the following:

nametypeupdatedselectedcolumn
dbo.Studentsuser tablenoyesId
dbo.Studentsuser tablenoyesName
dbo.Studentsuser tablenoyesStd_Course
dbo.Studentsuser tablenoyesPhone
dbo.Studentsuser tablenoyesStd_Grade





Explanation:

The above example displays the dependencies of the view, vwStudentDetails.

Language(s): MS SQL Server