Views - General Question

[Gerard]

Hey all,
I just need to know some rules for making Views
updateable. I have realized the hard way, that doing joins
(left or right) in the SQL pane of a view usually leads to
the view not being updateable. I know there is a way to
derive one view from another view. What I have read is
the best approach is to derive each view from one table
only(making it updateable). Then to link multiple views,
each representing one table, thus the derived view will be
updateable and accomplish the same as a join. Is this
correct, or am I full of it. I need Views that are
updateable, but derived from more then one table. Any
help is appreciated, thanks in advance.

Gerard

 

[Marshall Smith]

Joining multiple views is just as tricky to make updateable as joining
multiple tables.

First, make sure every table has a primary key defined. You must have a
primary key for the table to be updateable.

Second, make sure that the query in general is going to be updateable (e.g.,
no GROUP BY clause, no DISTINCT keyword). Also, make sure the query type is
"Updateable Snapshot."

Third, recognize that you will only be able to update the "most-many" table.
SQL Server just won't let you update the other tables, pretty much no matter
what you do.

After that, you may need to play with any given view a bit to make sure it
is updateable.