Building a foreign key table

[Cary]

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]

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]

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