Inserting or Updating a View

26 Jan 2006 3:32 PM

Paul

In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View?

For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
Woof has Columns WoofID(key) and WoofName(nvarchar).
SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))

I make a view:

SELECT     dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID,
dbo.zSonOfWoof.SonOfWoofName,
                      dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
dbo.zGrandSonOfWoof.zGrandSonOfWoofName
FROM         dbo.zWoof INNER JOIN
                      dbo.zSonOfWoof ON dbo.zWoof.WoofID =
dbo.zSonOfWoof.WoofID INNER JOIN
                      dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
dbo.zGrandSonOfWoof.zSonOfWoofID
But I can not find a way to use a Stored procedure to insert a column to
GrandsonOfWoof, for example.

Any recommendation would be greatly appreciated.. (Do I use an "Indexed
View"?)

TIA,

Paul
(woof!)

26 Jan 2006 9:08 PM

Hugo Kornelis

On Thu, 26 Jan 2006 10:32:09 -0500, Paul wrote:

Show quote

>In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View?
>
>For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
>Woof has Columns WoofID(key) and WoofName(nvarchar).
>SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
>GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
>GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))
>
>I make a view:
>
>SELECT     dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID,
>dbo.zSonOfWoof.SonOfWoofName,
>                      dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
>dbo.zGrandSonOfWoof.zGrandSonOfWoofName
>FROM         dbo.zWoof INNER JOIN
>                      dbo.zSonOfWoof ON dbo.zWoof.WoofID =
>dbo.zSonOfWoof.WoofID INNER JOIN
>                      dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
>dbo.zGrandSonOfWoof.zSonOfWoofID
>But I can not find a way to use a Stored procedure to insert a column to
>GrandsonOfWoof, for example.
>
>Any recommendation would be greatly appreciated.. (Do I use an "Indexed
>View"?)
>
>TIA,
>
>Paul
>(woof!)
>

Hi Paul (meow),

You can't insert rows in the tables through this view. Since the view
shows data from three tables, an INSERT might be equivalent to an INSERT
in all three base tables - and that is not supported.

If you can just insert into the base tables, do so.

If you really *have* to insert through this view, then check out INSTEAD
OF triggers. They are described in Books Online. If the help there is
not enough to get you going, then by all means come back here for more
help - but in that case, I'll have to ask you to use CREATE TABLE and
INSERT statements to explain your situation. See www.aspfaq.com/5006.

--
Hugo Kornelis, SQL Server MVP