INSERT Record through a view

Is is possible to insert a record through a view. If so, how?

USE Northwind

GO

CREATE TABLE tbForms (
FormID INT IDENTITY (1,1) NOT NULL,
Form varchar (100) NOT NULL
)

GO

ALTER TABLE tbForms
ADD CONSTRAINT tbForms_pk PRIMARY KEY (FormID)
GO

CREATE TABLE tbDoubleTeeForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
Height FLOAT,
Flange FLOAT,
Leg FLOAT,
LegCount INT
)

GO

ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_pk PRIMARY KEY (fkFormID)
GO

ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO

CREATE TABLE tbFlatPanelForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
HEIGHT FLOAT
)

GO

ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_pk PRIMARY KEY (fkFormID)
GO

ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO

CREATE VIEW MyProducts AS
SELECT fkFormID, Form FROM tbDoubleTeeForms UNION ALL
SELECT fkFormID, FOrm FROM tbFlatPanelForms

GO

-- How can I insert a new record, the pk of the forms table is identity.
-- Can this be done?
INSERT INTO MyProducts (Form)
VALUES ('My First Entry')
GO

SELECT * FROM MyProducts
GO

DROP VIEW MyProducts
GO

DROP TABLE tbFlatPanelForms
GO

DROP TABLE tbDoubleTeeForms
GO

DROP TABLE tbForms
GO

Mike Bithink it's imposible to do with view with union
in your case you woudl like to insert data into 3 tables

maybe tray insert data into 2 tables and triger to put data into 3-th table|||you can insert into a view but you can only affect one table.
so in the case of unions this is not possible
BUT......
you can however use an instead of trigger to check for which table the insert is going to and then instead of inserting through the view, you insert directly to the correct table.|||BOL:

Updatable Partitioned Views
If a local or distributed partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.

A view is considered an updatable partitioned view if:

The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).
The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables.|||rdjabarov
no partition mentioned. so went with the conservative option

hey
how about some liquor this friday?|||Scott,

I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know.|||Originally posted by rdjabarov
Scott,

I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know. Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?

-PatP|||Hell...he'll be still backing out of his driveway by the time I get across NJ

:D

and a shamless 2500th post...

And you should look into partitioned views...the contraints have to be very specific...

but updating the base table is the best performing method...

for the view, the optimizer will still look at a tables in the view...|||Originally posted by Brett Kaiser
and a shamless 2500th post... Uff da! That's a lot of postings! Congratulations.

I still think you should have posted #2500 into the Yak Corral!

-PatP|||Originally posted by Pat Phelan
Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?

-PatP

Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!|||Yeah, I had a car like that once...

;)

-PatP|||northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.

normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.

eternity ensues|||Originally posted by GDMI
Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!
Over the christmas holidays I drove a plymoth breeze from Windsor Ontario Canada -> South Padre Island, Texas -> Orlando Florida -> Back to Windsor

Just under 7000 kilometers if I remember correctly.

Great time though!

Mike B|||Originally posted by Ruprect
northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.

normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.

eternity ensues The best I can come up with is "Well duh!"

Why put the exits closer together? Who would want to get off? If you think that the highway inhales vigorously, you should have tried getting off the highway somewhere in northern Florida!

-PatP