How to Create and Use Updateable Views in SQL Server: A Complete Guide

Let's create tblEmployees table and populate it with some sample data.

SQL Script to create tblEmployee table:

Drop table tblEmployee
CREATE TABLE tblEmployee
(
  Id int Primary Key,
  Name nvarchar(30),
  Salary int,
  Gender nvarchar(10),
  DepartmentId int
)

Insert data into tblEmployee table

Insert into tblEmployee values (1,'Rambo', 5000, 'Male', 3)
Insert into tblEmployee values (2,'Azam', 3400, 'Male', 2)
Insert into tblEmployee values (3,'Zoe', 6000, 'Female', 1)
Insert into tblEmployee values (4,'Inza', 4800, 'Male', 4)
Insert into tblEmployee values (5,'Sofia', 3200, 'Female', 1)
Insert into tblEmployee values (6,'Samser', 4800, 'Male', 3)

Let's create a view, which returns all the columns from the tblEmployees table, except Salary column.

Code:

Create view vWEmployeesDataExceptSalary
as
Select Id, Name, Gender, DepartmentId
from tblEmployee

Select data from the view: A view does not store any data. So, when this query is executed, the database engine actually retrieves data, from the underlying tblEmployee base table.

Code:

Select * from vWEmployeesDataExceptSalary

Is it possible to Insert, Update and delete rows, from the underlying tblEmployees table, using view vWEmployeesDataExceptSalary?

Yes, SQL server views are updateable.

The following query updates, Name column from Azam to Ali Azam. Though, we are updating the view, SQL server, correctly updates the base table tblEmployee. To verify, execute, SELECT statement, on tblEmployee table.

Code:

Update vWEmployeesDataExceptSalary 
Set Name = 'Ali Azam' Where Id = 2

Along the same lines, it is also possible to insert and delete rows from the base table using views.

Delete from vWEmployeesDataExceptSalary where Id = 2

Insert into vWEmployeesDataExceptSalary values (2, 'Roni', 'Male', 2)

If a view is based on multiple tables, and if you update the view, it may not update the underlying base tables correctly. To correctly update a view, that is based on multiple table, INSTEAD OF triggers are used.