Importing a “flat” field into a Lookup Table format in SQL database
* I have over 1000 records in a table (Context_Tasks_DB), one field of which (termed Purpose) should really be a Lookup to another table (tblPurpose), but currently simply has text (only 30 unique text values are in that field and are therefore repeated...).
* I have created a table called tblPurpose with the desired, unique, lookup values (30 unique values).
* I have created a NEW field called PurposeNew that references the Lookup table.
I would like to "set" the value in PurposeNew from tblPurpose based on the text value in Purpose.
Currently my SQL query looks like this:
UPDATE Context_Tasks_DB INNER JOIN tblPurpose
ON Context_Tasks_DB.Purpose = tblPurpose.Purpose
SET Context_Tasks_DB.PurposeNew = tblPurpose.Purpose;
The above only creates a Query output of blank values and does not SET the Lookup field in PurposeNew at all.
The objective is to convert my "flat" field into a list/lookup and essentially "import" the flat file into a DB appropriate format. After this is done, I will delete that flat file field as this is essentially a one time only import.
Software/Hardware used:
Software/Hardware used:
ASKED:
October 29, 2010 10:54 PM
UPDATED:
November 5, 2010 6:56 PM
Answer Wiki:
Sorry for late response.
Your new lookup table (tblPurpose) should have two fields (an Id and a description), and you should join your tables on the description.
Something like this:
<pre>UPDATE Context_Tasks_DB
SET Context_Tasks_DB.PurposeNew = tblPurpose.PurposeId
from Context_Tasks_DB JOIN tblPurpose
ON Context_Tasks_DB.Purpose = tblPurpose.PurposeDescription</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
