How to create updatable views in TSQL

Question

How to make an updatable view in TSQL. Please provide simple examples that I can base my solution on.

Lately I was helping my friend with TSQL, and he asked me about updatable views. I thought it will be a good idea to leave my solution here for the future as a quick lookup.

For a good reference please take a look at msdn.

Answer 1

I am providing a very basic working example.

--Here is our base table
CREATE TABLE  [test_table]
(
   [a]   int primary key, 
   [b]   varchar(40),
   [c]   bigint,
   [d]   varchar(20),
)
GO

--Here is our view to the table
CREATE VIEW [test_view]     As SELECT b,a,d FROM test_table
GO

--As an example insert few entries
INSERT INTO test_table VALUES (0, 'abc', 2, '2011-10-13')
INSERT INTO test_table VALUES (1, 'abc', 2, '2011-10-13')
INSERT INTO test_table VALUES (3, 'abc', 2, '2011-10-13')

--Check if everything works fine
SELECT * FROM test_table 
SELECT * FROM test_view
GO

Since table and the view are fullfiling the msdn requierments (lookup), I am able to do the following on the view:

UPDATE test_view SET b = 'xyz', d = '2011-10-14' where a = 0
INSERT test_view VALUES ('xyz', 2, '2011-10-14')
DELETE test_view where a = 3
GO

SELECT * FROM test_table 
SELECT * FROM test_view
GO

Important: Bulk insert is allowed as well.

Good luck with programming.