Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Unanswered: Help with INSERT multiple tables in many-to-many relationship?

    I am relatively new to MS SQL (not a novice, but hardly a master).

    I am working on a content management application for a magazine publisher. It’s written in ASP (VB Script) and being created on Dreamweaver 8 with an MS SQL 2000 database. Now, I’m trying to decide the best and fastest approach to coding a complex INSERT and UPDATE function. My question isn’t as much about the SQL (although that will probably come up after I decide how to do this), but about the procedural steps and approach I shuold be taking to do this.

    Reporters will use an online form to enter their story into the system. It collects the usual data: Headline, byline, story content, and the story category (feature, opinion, entertainment, business, sports, etc.). Each story may belong to MULTIPLE categories (feature & business, for example).

    So, I’ve created three tables to support this many-to-many realtionship:

    Story
    Category
    StoryCat (a junction table with the IDs from both the other tables).

    The online form has a dropdown menu which pulls the available categories from the Categories table. When the reporter has entered the data I use ASP to performs the insert just as you would expect it to.

    The next step needs to be to update the StoryCat table so that it creates a new record with the StoryID of the record it just inserted, along with the CategoryID that was in that record.

    ------------------

    As I said, I’m not sure of the best way to do this.

    Should I just pull back the last record inserted and then create a procedure that would insert into the StoryCat table (which is what I’m thinking of doing on the confirmation page), or is there another approach I should take (perhaps some sort of temporary table or stored procedure?).

    Any and all help will be greatly appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85
    Never mind. I finally found a solution online that will work:

    http://lists.evolt.org/archive/Week-...09/029484.html

    It involves using form variables as primary keys to pull back the just-inserted record and then doing an UPDATE to the StoryCat table.

Posting Permissions

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