Why would you insert into a view in SQL Server? [closed]

Question

What is the benefit of inserting in a view vs. in tables directly? e.g. Scenario #1

What if the view has only a few columns from a table, are you still able to insert successfully? e.g. Scenario #2

Scenario #1:

CREATE TABLE dbo.tbl1 
(
    ID INT NOT NULL,
    NAME VARCHAR(32) NOT NULL  
);

CREATE VIEW dbo.vw_x 
AS
    SELECT ID, Name
    FROM dbo.tbl1
    WHERE ID = 2

INSERT INTO dbo.vw_x
SELECT 2, 'Name';

Scenario #2:

CREATE TABLE dbo.tbl1 
(
    ID INT NOT NULL,
    NAME VARCHAR(32) NOT NULL  
);

CREATE TABLE dbo.tbl2 
(
    ID INT NOT NULL,
    VALUE VARCHAR(32) NOT NULL  
);

CREATE VIEW dbo.vw_x 
AS
    SELECT t1.ID, t2.Value
    FROM dbo.tbl1 t1
    INNER JOIN dbo.tbl2 t2
        ON t1.ID = t2.ID
    WHERE t1.ID = 2

INSERT INTO dbo.vw_x
SELECT 2, 'Name';

Answer 1

Typically users might insert into a view if they have not been granted permissions to the underlying tables.

Regarding "how inserting happens", this is from the MSDN article for the CREATE VIEW statement:

Updatable Views

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:

  An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.

  A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.

• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.

• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table.