Modifying Data Through a View in SQL Server 2012

In this article, we will see how to modify the data of an underlying base table in SQL Server using a View. A View is defined as a virtual table with data from various tables in the database. Views are most commonly used to provide security because it ensures that a user can only access the data they are intended to access and any remaining data is neither seen nor can be accessed by them. The remaining data is thereby automatically protected from unwanted users. So let's have a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

I have a table named EmployeeDetail.

SELECT [emp_fname]

      ,[emp_lname]

      ,[emp_no]

      ,[emp_add]

FROM [master].[dbo].[EmployeeDetail]

Output

Creating a View

Syntax

CREATE VIEW View_Name (Column_Name1,Column2..)

WITH ENCRYPTION

AS

Select_Statement [WITH CHECK OPTION ]

Now the following query defines the view.

Create view UpdateView

AS

SELECT Emp_Fname,

Emp_Lname,

Emp_no,

Emp_Add

FROM [EmployeeDetail]

 

Modifying Data Through a View

 

UPDATE UpdateView

SET emp_fname='Ram Kumar', emp_no=12

WHERE emp_add='Delhi';

SELECT * FROM EmployeeDetail

 

Output

ALTERING View

Using the ALTER VIEW statement you can modify a view without dropping it. We can modify the view using the following query:

 

ALTER VIEW view_name

AS

SELECT statement

DROPPING View
 

We can delete the view using the drop command as follows:
 

DROP VIEW view_name

DROPPING Multiple Views

You can drop a single view or multiple views at a time.

Syntax

DROP VIEW view_name1, view_name2,..