Most frequent question in SQL Server interviews is How to Update, Insert and Delete data through Views in SQL Server.
To update, insert and delete data in a table through views in SQL Server, you need to understand the following.
Important
⦁ View – it is a virtual table that is created by joining one or more tables. it does not contain any physical data but provides a logical representation of the underlying data.
⦁ Views can be used to update, insert and delete data in the tables.
Update Views
⦁ View must contain only one table.
⦁ View must not use any subqueries.
⦁ It should not be use DISTINCT Keyword.
⦁ View must not use TOP keyword.
⦁ Can only update columns that belong to a single base table.
CREATE View EmployeeV AS SELECT * FROM Employee SELECT * FROM EmployeeV UPDATE EmployeeV SET hire_date = '2015-12-15' WHERE emp_id ='L-B31947F'
Insert Views
⦁ cannot insert a row if the view references more than one base table.
⦁ view cannot contain any aggregate function, GROUP BY or HAVING clauses.
⦁ view cannot contain any subqueries.
INSERT INTO EmployeeV(emp_id,fname,lname,job_id,pub_id) VALUES('SA1250543','JOHN','Fleming',12,0877)
Delete
⦁ You cannot delete a row if the view references more than one table.
⦁ It must contain all the columns from the underlying table that are part of the primary key or a unique constraint.
DELETE FROM EmployeeV WHERE emp_id = 'SA1250543'
