Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: 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 B

  2. #2
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    ithink 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

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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.
    Last edited by Ruprect; 04-20-04 at 12:55.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    rdjabarov
    no partition mentioned. so went with the conservative option


    hey
    how about some liquor this friday?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hell...he'll be still backing out of his driveway by the time I get across NJ



    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  10. #10
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19
    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!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yeah, I had a car like that once...



    -PatP

  12. #12
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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

  13. #13
    Join Date
    Feb 2004
    Posts
    134
    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

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •