Why would you insert into a view in SQL Server?

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';

Possible duplicate of [Is it possible to insert data into a MySQL view?](http://stackoverflow.com/questions/3825941/is-it-possible-to-insert-data-into-a-mysql-view) — Krease, Dec 15 '15 at 00:33
I pointed it out as the top answer there would likely be useful in answering your second question (in short, assuming the view supports inserting, then yes the values actually get inserted to the table). For the first part of your question, there are a ton of examples of doing this throughout the internet. — Krease, Dec 15 '15 at 16:02

score 10 · Accepted Answer · answered Dec 15 '15 at 01:17

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.

Thank you for the answer, I truly believe this can help future developers better understand the process. I am not sure why it was closed. — Tigerjz32, Sep 22 '18 at 17:39

Why would you insert into a view in SQL Server?

1 Answers1