OK, I've googled the heck out of this one, but i don't know how to explain my problem to a search engine. I have 2 existing tables on SQL Server that I need to create a relationship between. I inherited this database and the table structure doesn't exactly follow the "rules".
Primary table has Table1ID (int) and JobNo (int)
Foreign table has Table2ID (int) and JobNo (int)
Each has 1,000s of records with Table2 - JobNo having multiple duplicates, which is ok.
In Table2 I need to create a field Table1ID to act as a foreign key to Table1.
What kind of query do I need to run to go through each record of Table2 and cross-reference the JobNo of both tables then insert the corresponding Table1ID into Table2's Table1ID.
In other words I need a foreign key value in Table2 where the two JobNo fields match.
Hope that is not too confusing.