Create an Updatable View in SQL Server

Hi Vipendra,

Sql server view is only updatable when the views comes under only one table.

If the view has joins with the multiple table then it can't update.

For example,

CREATE VIEW VW_Employee
AS
BEGIN
SELECT FirstName,LastName,Address,City,State,Country
FROM Employees
END

If you want to update this then
UPDATE VW_Employee SET FirstName='Senthil', LastName='Kumar',City='BANGALORE',State='Tamilnadu',Country='India'

If the views belongs to multiple table then

CREATE VIEW VW_Employee
AS
BEGIN
SELECT E.FirstName,E.LastName,E.Address,E.City,E.State,E.Country,S.Salary,S.Month,S.Year
FROM Employees E INNER JOIN Salary S ON S.EmployeeID = E.EmployeeID
END

This table can't be updated as well as deleted.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Hi Vipendra,

Sql server view is only updatable when the views comes under only one table.

If the view has joins with the multiple table then it can't update.

For example,

CREATE VIEW VW_Employee
AS
BEGIN
SELECT FirstName,LastName,Address,City,State,Country
FROM Employees
END

If you want to update this then
UPDATE VW_Employee SET FirstName='Senthil', LastName='Kumar',City='BANGALORE',State='Tamilnadu',Country='India'

If the views belongs to multiple table then

CREATE VIEW VW_Employee
AS
BEGIN
SELECT E.FirstName,E.LastName,E.Address,E.City,E.State,E.Country,S.Salary,S.Month,S.Year
FROM Employees E INNER JOIN Salary S ON S.EmployeeID = E.EmployeeID
END

This table can't be updated as well as deleted.
Satyapriya Nayak14y ago
Hi Vipendra,

Please refer the below link

http://msdn.microsoft.com/en-us/library/ms187956.aspx

http://cactussoft.com/ITUtopia/?p=36

http://www.java2s.com/Tutorial/SQLServer/0320__View/0140__Updatable-View.htm

Thanks