Importing a “flat” field into a Lookup Table format in SQL database

5 pts.
Tags:
Microsoft Access
SQL
SQL Database
SQL Query
SQL Records
SQL Server
    * 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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following