Wednesday 12 June 2013

Insert into View in sql server

Hi,
      In privious article I have discussed about Basic of view . Now we will discuss about a how
to create a view on single table and how to insert data or records in table by using view.
example::

Create table::

create table emp_master (emp_id int identity(1,1) ,emp_name varchar(100),emp_sal varchar(100) ,dept_id int)

Insert records in table::


insert into emp_master (emp_name,emp_sal,dept_id ) values('sandipG','1000',1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('ajit','45321' ,1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('pranav','5000',2)
insert into emp_master (emp_name,emp_sal,dept_id ) values('aditya','10000',3)


1)Create view on single table with select all columns in table .

Create view [dbo].View_on_single_table_1
as
select * from emp_master

Test Case::

insert into View_on_single_table_1 (emp_name,emp_sal,dept_id)values('sachin','2123',5) select * from emp_master

2)Create view on single table with select Two columns in table .


Create view [dbo].View_on_single_table_2
as
select emp_name,emp_sal from emp_master

Test Case 1::

insert into View_on_single_table_2 (emp_name,emp_sal,dept_id)values('sachin','2123',5)
select * from emp_master

you Get error when you try to exicute above statement becouse you are not
selected dept_id in view .


Test Case 2::


insert into View_on_single_table_2 (emp_name,emp_sal)values('sachin','2123')
select * from emp_master

Related links ::
basic of view
update trigger
Insert trigger
delete trigger
basic of trigger

No comments:

Post a Comment

if you have any doubt any suggestions do comment