Building a foreign key table

Discussion in 'ASP General' started by Cary, Aug 5, 2003.

  1. Cary

    Cary Guest

    I have a table of data that contains all of the information I need to use
    for a product look-up by application. The table originated as an Excel file
    from the product manufacturer that I have imported into MsSQL. This table
    includes the following fields, year, make, model, engine, product, part_no
    and notes.

    Because the information in each column is very repetitive I created new
    tables named, Years, Makes, Models, Products, PartNums and Notes and
    populated them with the redundant data from the original table using a
    series of SQL statements. Now I have the tables created and populated with
    the application information.

    Now my problem is creating the foreign key table to look-up the product and
    part numbers by the application. I would like to use a SQL statement to
    update the information in the original table with id's from the other
    tables. How can I do this? I don't mind creating another table, but the
    original table contains the same application information I need.

    Any help is appreciated!
    Cary, Aug 5, 2003
    #1
    1. Advertising

  2. Cary

    Alan Guest

    If your data table is called, ummm, Data, then create additional int columns
    for each foreign key. You'll use SQL something like:

    UPDATE Data SET MakeID = (SELECT m.MakeID FROM Makes m WHERE m.MakeDesc =
    MakeDesc)

    Where (L->R) MakeID is the new int FK column for the Make, m.MakeID is the
    PK for the Make from the Makes table, and the two MakeDesc columns are the
    two descriptive Make columns that are the focus of your normalisation. Not
    the nicest soln, but it should do what you're after.

    Do this for each FK column, and when you're done you can drop the original
    descriptive columns from the Data table.

    Hope this helps.

    Alan


    "Cary" <> wrote in message
    news:uED#...
    > I have a table of data that contains all of the information I need to use
    > for a product look-up by application. The table originated as an Excel

    file
    > from the product manufacturer that I have imported into MsSQL. This table
    > includes the following fields, year, make, model, engine, product, part_no
    > and notes.
    >
    > Because the information in each column is very repetitive I created new
    > tables named, Years, Makes, Models, Products, PartNums and Notes and
    > populated them with the redundant data from the original table using a
    > series of SQL statements. Now I have the tables created and populated with
    > the application information.
    >
    > Now my problem is creating the foreign key table to look-up the product

    and
    > part numbers by the application. I would like to use a SQL statement to
    > update the information in the original table with id's from the other
    > tables. How can I do this? I don't mind creating another table, but the
    > original table contains the same application information I need.
    >
    > Any help is appreciated!
    >
    >
    Alan, Aug 5, 2003
    #2
    1. Advertising

  3. Cary

    Cary Guest

    Thank you,

    This worked perfectly, it is exactly what I wanted to do. I just could not
    figure out how to run the subquery.

    Thank you for your help!

    Cary

    "Alan" <> wrote in message
    news:%...
    > If your data table is called, ummm, Data, then create additional int

    columns
    > for each foreign key. You'll use SQL something like:
    >
    > UPDATE Data SET MakeID = (SELECT m.MakeID FROM Makes m WHERE m.MakeDesc =
    > MakeDesc)
    >
    > Where (L->R) MakeID is the new int FK column for the Make, m.MakeID is the
    > PK for the Make from the Makes table, and the two MakeDesc columns are the
    > two descriptive Make columns that are the focus of your normalisation. Not
    > the nicest soln, but it should do what you're after.
    >
    > Do this for each FK column, and when you're done you can drop the original
    > descriptive columns from the Data table.
    >
    > Hope this helps.
    >
    > Alan
    >
    >
    > "Cary" <> wrote in message
    > news:uED#...
    > > I have a table of data that contains all of the information I need to

    use
    > > for a product look-up by application. The table originated as an Excel

    > file
    > > from the product manufacturer that I have imported into MsSQL. This

    table
    > > includes the following fields, year, make, model, engine, product,

    part_no
    > > and notes.
    > >
    > > Because the information in each column is very repetitive I created new
    > > tables named, Years, Makes, Models, Products, PartNums and Notes and
    > > populated them with the redundant data from the original table using a
    > > series of SQL statements. Now I have the tables created and populated

    with
    > > the application information.
    > >
    > > Now my problem is creating the foreign key table to look-up the product

    > and
    > > part numbers by the application. I would like to use a SQL statement to
    > > update the information in the original table with id's from the other
    > > tables. How can I do this? I don't mind creating another table, but the
    > > original table contains the same application information I need.
    > >
    > > Any help is appreciated!
    > >
    > >

    >
    >
    Cary, Aug 6, 2003
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. TSA
    Replies:
    1
    Views:
    725
  2. H5N1
    Replies:
    0
    Views:
    436
  3. Adam
    Replies:
    2
    Views:
    437
    dontlikenicknames
    Sep 2, 2008
  4. snehasish
    Replies:
    0
    Views:
    1,622
    snehasish
    Oct 27, 2009
  5. Lionel
    Replies:
    9
    Views:
    3,390
Loading...

Share This Page