partypants
asked on
Create relationship between 2 existing tables - SQL Server
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry a forgot a )
Create Table3 (
Table1ID int null
,JobNo int null)
Create Table3 (
Table1ID int null
,JobNo int null)
ASKER
The poster replied to correct a missing )
ASKER
Thank You Thank You!!!! Exactly what I wanted to do!!!
If JobNo is unique in Table1 then it seems logical to make that the foreign key in Table2.