Inserting Data through Views

You can insert values into views in the same way that you insert values into tables. However, you must be careful to only insert values for columns that belong to a single table. If a view includes several tables, the insert values can only manage columns from one of the tables. For example, the CategoryName column cannot be updated in the following view.

Inserting a Value into a View

1. Open SQL Server Management Studio and connect to your server.

2. Click the New Query button and type the following:

    USE [AdventureWorks] GO CREATE VIEW [dbo].[vProductFullCategories] AS 

    SELECT    Production.ProductSubcategory.ProductSubcategoryID,    Production.ProductCategory.Name AS CategoryName,     Production.ProductSubcategory.Name FROM    Production.ProductSubcategory INNER JOIN    Production.ProductCategory       ON Production.ProductSubcategory.ProductCategoryID       = Production.ProductCategory.ProductCategoryID GO 

3. Try to insert a row by using the following sentence :

    INSERT INTO [AdventureWorks].[Production].[vProductFullCategories]            ([ProductSubcategoryID]            ,[CategoryName]            ,[Name])      VALUES            (1            ,'Bikes'            ,'My New Subcategory') 

   You will receive the following error:

    Msg 4405, Level 16, State 1, Line 1 View or function 'AdventureWorks.Production.vProductFullCategories' is not updatable because the modification affects multiple base tables. 

Using the WITH Clause

You can use a WITH clause to obtain recursive information to insert into another table. This is a good way to reconstruct multilevel relational information into a single table.

Consider the following example: The managers at Adventure Works want to send a special Christmas e-mail to all employees. They decide that each manager should send the e-mail to his own employees. You can create a table containing information about each managers employees and the senders e-mail address.

The table can be constructed as follows :

   CREATE TABLE [Person].[ChristmasMails](    [EMailID] [int],    [FirstName] [dbo].[Name] NOT NULL,    [LastName] [dbo].[Name] NOT NULL,    [EmailAddress] [nvarchar](50)  NULL,    [EmailSender] [nvarchar](50)  NULL ) GO   

Using a common table expression, you can then build the entire list of destinations, with the senders e-mail address included as one of the columns in the query. Since one of the employees is the top manager, that person will not receive an e-mail and the senders address will be an empty string.

 WITH MailDestinations(EmployeeID, FirstName, LastName,     EmailAddress, EMailSender) AS (    SELECT   HumanResources.Employee.EmployeeID,          Person.Contact.FirstName,          Person.Contact.LastName,          Person.Contact.EmailAddress,          CONVERT(nvarchar(50),'') AS EMailSender       FROM HumanResources.Employee          INNER JOIN Person.Contact             ON HumanResources.Employee.ContactID = Person.Contact.ContactID       WHERE     (HumanResources.Employee.ManagerID IS NULL)    UNION ALL    SELECT   e.EmployeeID,          Person.Contact.FirstName,          Person.Contact.LastName,          Person.Contact.EmailAddress,          d.EmailAddress AS EmailSender       FROM HumanResources.Employee AS e          INNER JOIN Person.Contact             ON e.ContactID = Person.Contact.ContactID          JOIN MailDestinations AS d             ON e.ManagerID = d.EmployeeID ) INSERT INTO [Person].[ChristmasMails]    SELECT * FROM MailDestinations 

The first SELECT statement selects the information about the top manager who has no assigned Manager ID. The second section makes recursive queries to obtain information about those employees who are supervised by the top manager. The query then recurses to obtain information about the employees of those who are already in the common table expression until no more employees are found. The execution of this query will add 290 records to the [Person].[ChristmasMails] table.

Using INSTEAD OF INSERT Triggers on Views

Suppose that you use the vProductFullCategories statement to give users the opportunity to obtain information from an Office application, such as Excel. However, the user tries to use the same view from Microsoft Access, assuming he has no rights to insert information into the tables. In fact, the user can do that if he has insert rights on the view. Access tries to insert values for all of the columns, including those belonging to other tables.

You can use an INSTEAD OF INSERT trigger on the view to discard those values. Moreover, you can manage the insertion in several related tables inside a view. The trigger, like any trigger, uses a special table named inserted that receives the values defined by the user. The trigger can select from this table to insert the appropriate values in one or more tables.

Adding the following INSTEAD Of INSERT trigger allows you to manage the insertion properly.

 CREATE TRIGGER [Production].[vProductFullCategoriesInsertTrigger]    on [Production].[vProductFullCategories] INSTEAD OF INSERT AS BEGIN    DECLARE @idCategory INT -- Retrieve the id from the ProductCategory table    SET @idCategory=       (SELECT ProductCategoryID          FROM Production.ProductCategory          WHERE     (Name =             (SELECT CategoryName FROM inserted)          )       ) -- Insert the proper values INSERT INTO [AdventureWorks].[Production].[ProductSubcategory]            ([ProductCategoryID]            ,[Name]            ,[rowguid]            ,[ModifiedDate])      SELECT @IdCategory            ,inserted.name            ,newid()            ,getdate() FROM inserted END