Best practices when inserting data into a view

Question

I have a few views and I want to insert some data into them. I've heard you need to "be careful when doing this", and you need to "setup the view to make the insert work correctly". What do I need to look for, and what if anything do I need to do to my view to allow insertions into it?

Is better to insert into the underlying tables even though it is pretty cumbersome?

Answer 1

You cannot INSERT INTO vwMyCompexView using T-SQL, nor can you use SQL Management Studio's grid GUI to insert data into a view that is composed of an underlying join.

You CAN use the SQL Mgmt Studio grid GUI or T-SQL to insert data into a view that is composed of a single table, that DOESN'T perform any computations or aggregate functions. Only those views who are a straight mirror of the base table can be inserted to. If your view excludes a column that is non-nullable and has no default value, the insert would fail.

My recommendation would be to always script your inserts by using stored procedures. (I actually had to TRY those two scenarios before answering this question. I'd never considered using a view to insert data. That's just me; YMMV.)

Answer 2

From MSDN, Modifying Data Through a View

• single base table can be updated
• no aggregates or fancy joins
• no row limiters (eg TOP)

Personally, I'd use INSTEAD OF/BEFORE triggers as the article mentions if I could not use stored procedures and I had to allow updatable views