Updatable views in SQL Server

Updatable views in SQL Server

As we know view is a virtual table .It’s nothing more then to saved only sql query. View can update on single table.

Example :

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

Insert into tblEmp values (1,’J’, 5000, ‘Male’, 3)
Insert into tblEmp values (2,’M’, 3400, ‘Male’, 2)
Insert into tblEmp values (3,’P’, 6000, ‘Female’, 1)
Insert into tblEmp values (4,’T’, 4800, ‘Male’, 4)
Insert into tblEmp values (5,’S’, 3200, ‘Female’, 1)
Insert into tblEmp values (6,’B’, 4800, ‘Male’, 3)

Create view vWEmpDataExceptSalary
as
Select Id, Name, Gender, DepartmentId
from tblEmp

Select * from vWEmpDataExceptSalary

Update vWEmpDataExceptSalary 
Set Name = ‘Mikey’ Where Id = 2

That’s it if we update tow or more table it’s going wrong because A VIEW does not require any storage in a database because it does not exist physically.

Note: SQL Develop Suggest to not update on View.