Insert into a not-updatable view : Updatable View « View « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. View
3. Updatable View

3> CREATE TABLE one
4> (
5>     col11    int    NOT NULL,
6>     col12    int    NOT NULL
7> )
8>
9> CREATE TABLE two
10> (
11>     col21    int    NOT NULL,
12>     col22    int    NOT NULL
13> )
14> GO
1>
2> CREATE VIEW one_two
3> AS
4> (SELECT col11, col12, col21, col22
5> FROM one LEFT JOIN two ON (col11=col21))
6> GO
1>
2> INSERT one_two (col11, col12)
3> VALUES (1, 2)
4> GO

(1 rows affected)
1> SELECT * FROM one_two
2> GO
col11       col12       col21       col22
----------- ----------- ----------- -----------
          1           2        NULL        NULL

(1 rows affected)
1>
2> INSERT one_two (col11, col12, col21, col22)
3> VALUES (1, 2, NULL, NULL)
4> GO
Msg 4405, Level 16, State 1, Server J\SQLEXPRESS, Line 2
View or function 'one_two' is not updatable because the modification affects multiple base tables.
1> drop view one_two;
2> drop table one;
3> drop table two;
4> GO
1>