DML on View

Many times question came in our mind and we got confused about whether we can perform the data manipulation operations on views or not. The answer to this question is, it depends on how we have created the views. Let’s take a simple example to understand it.

CREATE TABLE Employees(EmployeeId int Not Null Identity (1,1), EmployeeName varchar(100))
GO
INSERT INTO Employees(EmployeeName) VALUES ('Abc')
GO
INSERT INTO Employees(EmployeeName) VALUES ('Xyz')
GO
SELECT * FROM Employees
GO

View1

Now create the view on employee table as shown below and try to insert the value using it.

CREATE VIEW vw_Employees AS SELECT * FROM Employees WITH CHECK OPTION
GO
INSERT INTO vw_Employees (EmployeeName) VALUES ('Pqr')
GO
SELECT * FROM vw_Employees 
GO

View2

As shown in above fig. data successfully inserted into the employee table using view. To understand how “WITH CHECK OPTION” work along with view. Let’s take another example. Create another table employee2 and create view on it with where clause to filter the data for employee id 2 using below SQLs

CREATE TABLE Employees2(EmployeeId int, EmployeeName varchar(100))
GO
INSERT INTO Employees2(EmployeeId, EmployeeName) VALUES (1,'Abc')
GO
CREATE VIEW vw_Employees2 AS SELECT * FROM Employees2 WHERE EmployeeId = 2 WITH CHECK OPTION
GO
SELECT * FROM Employees2 
GO

View3

As you can see, there is one record in employee2 table which we have inserted using insert statement on table. We have created view vw_employee2 on employee2 table with filter on employeeid 2. Now try to insert the record for employee id 2 first and check the result.

INSERT INTO vw_Employees2 (EmployeeId, EmployeeName) VALUES (2,'asd')
GO
SELECT * FROM vw_Employees2

View4

As you can see, data inserted successfully in table. Now try to insert the record in employee2 table using view for employeeid 1.

INSERT INTO vw_Employees2 (EmployeeId, EmployeeName) VALUES (1,'Asdf')
GO

It throws the below SQL exception

Msg 550, Level 16, State 1, Line 25
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Summary: Yes we can perform the DML operation on View but it depends on how we have created it.

Advertisements

#dml-on-view