UPDATEABLE VIEWS IN SQL SERVER 2005

We can use views to update the data in the actual table. Please refer the below example for details.

–CREATE a table
CREATE TABLE EMPLOYEE
(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EMPNAME VARCHAR(100),
SALARY DECIMAL
)

–Enter some data
SELECT * FROM employee

OUTPUT
________________

1 Pawan 180000
2 Gaurav 80000
3 Saurabh 80000
4 Pawan 830000
5 Neelam 1000

–CREATE A VIEW
CREATE VIEW View1
AS
SELECT * from employee

–Check data in the view
SELECT * FROM View1

OUTPUT
________________

1 Pawan 180000
2 Gaurav 80000
3 Saurabh 80000
4 Pawan 830000
5 Neelam 1000

–Use the below command to update data in the table using view.
Update view1
set salary = 200000
where EmpName = ‘Neelam’

OUTPUT
________________

SELECT * FROM View1
1 Pawan 180000
2 Gaurav 80000
3 Saurabh 80000
4 Pawan 830000
5 Neelam 200000

OUTPUT
________________

SELECT * FROM employee

1 Pawan 180000
2 Gaurav 80000
3 Saurabh 80000
4 Pawan 830000
5 Neelam 200000

Note : The above example contains only one table. In the next post i
will add some more table and see whether we can actually update the
data or not.

Pawan Kumar
Pawankkmr@hotmail.com

Advertisements