Insert and Update
Statements against Views in SQL Server:
The views are basically
allows the user to fetch the records from one or more tables. The views may or
may not return all the columns from their base tables. So the view can only
show the data in the columns those are specified while creating it.
Let’s create a table
called dbo.Table_1 and a view as shown below.
CREATE TABLE [dbo]. [TABLE_1]
(
[UID] [int] NOT NULL,
[NAME] [Nchar](10) NULL,
[NEWC] [Varchar](20) NULL
)
CREATE VIEW [dbo].[vw_test1]
AS
SELECT NAME, NEWC FROM [dbo]. [TABLE_1]
And I have inserted data
into the base table as shown below.
INSERT INTO dbo.TABLE_1 SELECT 1, 'one', 'one one'
INSERT INTO dbo.TABLE_1 SELECT 2, 'two', 'two two'
INSERT INTO dbo.TABLE_1 SELECT 3, 'the', 'the the'
The view dbo.vw_test1 is
currently returning only two columns are NAME and NEWC. So when I try to fetch
the data from that view, it will show only data from these two columns as shown
below.
SELECT * FROM
dbo.vw_test1
INSERT statement against
View:
The view does not store
any data. It can fetch and/or write data onto its base table.
First of all we need to
know what all the columns that view can return are. In this case the view
dbo.vw_test1 can return only two columns so that we can do INSERT/UPDATE data
only on these two columns on its base table ‘dbo.TABLE_1’.
Case1: Insert Data
When we try to insert
data against the view dbo.vw_test1, then we can pass only two values as the
view having only two columns. And these two columns type are nchar (10) and
varchar (20), so we need to specify the string values while inserting data
against the view.
INSERT INTO dbo.vw_test1
SELECT ‘the’, ‘the the’
The above INSERT
statement will throws an error as shown below.
The above error indicates
that the UID column in the dbo.TABLE_1 does not allow the NULL values as it is
defined as ‘NOT NULL’ column.
So the view can pass the
values as it is for the columns NAME and NEWC to its base table, and also it is
trying to pass the NULL value into the other column UID as we did not defined
any value for this column UID in our actual INSERT statement. Since this column
UID has been defined as NOT NULL, so it throws an error.
In this case, we can not
able to insert data against the view vw_test1.
Case2: Insert Data
Now I am creating another
view where we can able to execute INSERT statement onto it.
CREATE VIEW dbo.vw_test2
AS
SELECT UID, NAME FROM [dbo]. [TABLE_1]
GO
The above view is
currently returning two columns UID and NAME. So in this case, we are going to
define the values for UID and NAME columns in the below INSERT statement. And
the NULL data will be propagated into the other column NEWC.
INSERT INTO dbo.vw_test2
SELECT 100, ‘Hundred’
The above insert
statement has been inserted data successfully onto the table dbo.TEST_1. You
can see the results below.
Update statement against
View:
Now we can able to update
data without having any issues on the columns which are currently returning
data from the views.
View1: dbo.vw_test1
The view vw_test1 can
allows us to execute update statements in order to update data only on NAME and
NEWC columns. It won’t allow us to update data on any other columns in it base
table and also we can not use these other columns in WHERE clause.
UPDATE dbo.vw_test1 set
NAME = ‘update’, NEWC = ‘updated by’
The data will be updated
as shown below after execution of the above UPDATE statement.
View2: dbo.vw_test2
The view vw_test2 can
allows us to execute update statements on it to update data only on UID and
NAME columns. The following update statement will update data in UID and NAME
columns.
UPDATE dbo.vw_test2 set
UID = 10, NEWC = ‘update by V2’
The data will be updated
as shown below after execution of the above UPDATE statement.
DELETE Data Using the
Views:
Like tables, we can
execute the DELETE statements against the user defined views to delete data.
The DELETE statement can only executed if the view is returning the columns
only from the base table. If the view is returning columns more than one
column, the following error will throws out.
