How to Insert and Delete Rows in Base Table Using View in SQL

In this article I am going to explain how to insert and delete rows using view in sql.
  • 1803

Introduction

View in SQL is a virtual table and this table is created on the result set of SQL statement. View is similar to a normal table and also have rows and columns. View may be combination  of some rows and columns of two or more than two tables. You can use SQL function and WHERE clause with View in SQL statement.

You can insert and delete rows into base table using view. To insert rows we use INSERT INTO statement. To delete row from base table using view we use DELETE clause. To create view we use CREATE VIEW statement with WHERE clause.

A Statement that create vendors table

create table vendors

 (

 vendorid int,

 vendorname varchar(15),

 vendorcity varchar(15),

 vendorstate varchar(15)

 )

Statements that insert data in vendors table

insert into vendors values (20,'vipendra','noida','up')

insert into vendors values (21,'deepak','lucknow','up')

insert into vendors values (22,'rahul','kanpur','up')

insert into vendors values (23,'malay','delhi','delhi')

insert into vendors values (24,'mayank','noida','up')

A Statement that is used to fetch data from vendors table

ven (2).jpg

A Statement that is used to create a view in sql

create view vendorinfo

as

select vendorid,vendorname

from vendors

where vendorcity='noida'


A Statement that show data in view


8.jpg

A Statement that is used to insert row in base table using view

insert into vendorinfo values (40,'neha')


Statement that show data in vendor table after insertion of data


Clipboard0111111.jpg

A Statement that is used to delete row from base table using view

delete from vendorinfo where vendorid=40


Statement that show data in vendor table after delete statement


Clipboard161.jpg


© 2013 dotNetheaven. All rights reserved.