T-SQL » Views » Modifying Data Through a View
- Syntax:
-
- Insert data using a view:
INSERT [ INTO ]
view_name
[ ( column1, ... ) ]
VALUES ( value1, ... )
- Update data using a view:
UPDATE view_name
SET column1=value1, ...
[ WHERE condition ]
- Remove Data using a view:
DELETE
[ FROM ]
view_name
[ WHERE condition ]
- To get information about a view:
sp_helptext view_name
- Dependencies of a view:
sp_depends view_name
- Insert data using a view:
- 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)
Id Name Std_Course Phone Std_Grade 20 Billy James 4 27751 2
(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:
name type updated selected column dbo.Students user table no yes Id dbo.Students user table no yes Name dbo.Students user table no yes Std_Course dbo.Students user table no yes Phone dbo.Students user table no yes Std_Grade
Explanation:
The above example displays the dependencies of the view, vwStudentDetails.
Language(s): MS SQL Server
