Professional Web Applications Themes

SQL7 Views and INSERT - Microsoft SQL / MS SQL Server

A pretty simple question. We are using SQL7 and 2000 in a LARGE mixed server environment, so I am pretty much stuck using "standard" SQL7. I created a new table from three exsiting tables that contained "basicly" the same information, the main difference being each table was specific to a different manufacturing location (with different prices and item codes for items, but the format of the data is identical). SO...the new table is exactly the same as the original tables with the addition of a location field. Now, I got clever and created three views that access the table but ...

  1. #1

    Default SQL7 Views and INSERT

    A pretty simple question. We are using SQL7 and 2000 in a
    LARGE mixed server environment, so I am pretty much stuck
    using "standard" SQL7. I created a new table from three
    exsiting tables that contained "basicly" the same
    information, the main difference being each table was
    specific to a different manufacturing location (with
    different prices and item codes for items, but the format
    of the data is identical). SO...the new table is exactly
    the same as the original tables with the addition of a
    location field. Now, I got clever and created three views
    that access the table but using the location field as a
    non-displayed filter AND, to be able to re-use a vast
    amount of old code, just named the views with the old
    table names. Clever, except it doesn't exactly work!
    SELECT, UPDATE, and DELETE work just fine, but trying to
    INSERT yeilds the old "Insert Error: Column name or number
    of supplied values does not match table definition". Is
    there any way to make this thing work? Or will I have to
    modify several hundred stored procedures that access the
    old tables.
    Mike Brooks Guest

  2. #2

    Default Re: SQL7 Views and INSERT

    Mike,

    What's the big deal with having the Location column in the view as well?

    --

    Andrew J. Kelly
    SQL Server MVP


    "Mike Brooks" <mike.brooksmonacocoach.com> wrote in message
    news:04d801c347fe$b4ff95a0$a301280aphx.gbl...
    > A pretty simple question. We are using SQL7 and 2000 in a
    > LARGE mixed server environment, so I am pretty much stuck
    > using "standard" SQL7. I created a new table from three
    > exsiting tables that contained "basicly" the same
    > information, the main difference being each table was
    > specific to a different manufacturing location (with
    > different prices and item codes for items, but the format
    > of the data is identical). SO...the new table is exactly
    > the same as the original tables with the addition of a
    > location field. Now, I got clever and created three views
    > that access the table but using the location field as a
    > non-displayed filter AND, to be able to re-use a vast
    > amount of old code, just named the views with the old
    > table names. Clever, except it doesn't exactly work!
    > SELECT, UPDATE, and DELETE work just fine, but trying to
    > INSERT yeilds the old "Insert Error: Column name or number
    > of supplied values does not match table definition". Is
    > there any way to make this thing work? Or will I have to
    > modify several hundred stored procedures that access the
    > old tables.

    Andrew J. Kelly Guest

  3. #3

    Default Re: SQL7 Views and INSERT


    The location field is not exposed. Here's an example:
    OLD TABLES t_bend (bfield1, bfield2),
    t_seattle (sfield1, sfield2),
    t_portland (pfield1, pfield2).
    The new tables is
    t_combined (field1, field2, location)
    and the views are
    create view t_bend as select field1, field2 from t_combined where
    location = 'bend', etc.

    I can UPDATE, SELECT, DELETE data with this but cannot insert data
    becasue the old tables never had a location parameter/didn't need one.
    IS there some way of the table "knowing" which view is trying to insert
    and supply the missin field, or can I construct or default value *in the
    views* for the location.

    It is pretty simple to see why the SELECT, UPDATE, DELETE states work -
    the location field is resolved in the table already whereas with the
    INSERT statement it is not


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Mike Brooks Guest

  4. #4

    Default Re: SQL7 Views and INSERT

    A SQL 2000 solution is to create INSTEAD OF INSERT triggers on the
    views. You can hard-code the proper location value when inserting into
    the table from the triggers.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    [url]http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800[/url]
    [url]http://www.sqlserverfaq.com[/url]
    [url]http://www.mssqlserver.com/faq[/url]
    -----------------------

    "Mike Brooks" <mike.brooksmonacocoach.com> wrote in message
    news:04d801c347fe$b4ff95a0$a301280aphx.gbl...
    > A pretty simple question. We are using SQL7 and 2000 in a
    > LARGE mixed server environment, so I am pretty much stuck
    > using "standard" SQL7. I created a new table from three
    > exsiting tables that contained "basicly" the same
    > information, the main difference being each table was
    > specific to a different manufacturing location (with
    > different prices and item codes for items, but the format
    > of the data is identical). SO...the new table is exactly
    > the same as the original tables with the addition of a
    > location field. Now, I got clever and created three views
    > that access the table but using the location field as a
    > non-displayed filter AND, to be able to re-use a vast
    > amount of old code, just named the views with the old
    > table names. Clever, except it doesn't exactly work!
    > SELECT, UPDATE, and DELETE work just fine, but trying to
    > INSERT yeilds the old "Insert Error: Column name or number
    > of supplied values does not match table definition". Is
    > there any way to make this thing work? Or will I have to
    > modify several hundred stored procedures that access the
    > old tables.

    Dan Guzman Guest

  5. #5

    Default Re: SQL7 Views and INSERT

    Mike,

    I see now, you don't want to change any code to add the location. The only
    thing I can think of for a 7.0 solution is if the users were distinguishable
    between the 3 locations somehow. Then you could set a default and add
    something in the trigger to replace the default with the proper location.
    Other than that I don't know of any tricks that could help you out.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Mike Brooks" <anonymousdevdex.com> wrote in message
    news:OOQObhASDHA.2056TK2MSFTNGP12.phx.gbl...
    >
    > The location field is not exposed. Here's an example:
    > OLD TABLES t_bend (bfield1, bfield2),
    > t_seattle (sfield1, sfield2),
    > t_portland (pfield1, pfield2).
    > The new tables is
    > t_combined (field1, field2, location)
    > and the views are
    > create view t_bend as select field1, field2 from t_combined where
    > location = 'bend', etc.
    >
    > I can UPDATE, SELECT, DELETE data with this but cannot insert data
    > becasue the old tables never had a location parameter/didn't need one.
    > IS there some way of the table "knowing" which view is trying to insert
    > and supply the missin field, or can I construct or default value *in the
    > views* for the location.
    >
    > It is pretty simple to see why the SELECT, UPDATE, DELETE states work -
    > the location field is resolved in the table already whereas with the
    > INSERT statement it is not
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Andrew J. Kelly Guest

Similar Threads

  1. Question Insert from ASP to SQL using recordset data as values in insert statement
    By JasonM in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 13th, 05:54 PM
  2. views in 8.0
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 11th, 01:26 PM
  3. Replies: 3
    Last Post: September 30th, 09:24 PM
  4. Replies: 4
    Last Post: July 8th, 07:00 AM
  5. Default Value in a Table on SQL7
    By Narayana Vyas Kondreddi in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 07:49 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139