Complex query with multiple normalized fields We have a fairly simple table structure, but with a LOT of fields per table (talking 40+).  This data is initially produced in plain-text, user-readable tables, but then it is translated into higher-performance, easier to query tables before being installed for use in production.

What we do is, wherever possible and reasonable we translate certain fields into enumerated values, and keep track of the enumerations in a MasterEnum table.  There are usually 20-25 enumerated fields out of 40 or so.

Sample table structure:

Plain text version:


    |  PartNumber   |  Manufacturer  |  SomeData  |  SomeMoreData  |  SomeTextData ...
    ----------------------------------------------------------------------------------
    |  1x9kdah      |  GizmoCorp     | ThisIsData |  OtherData     |  ThisStaysText ...
    |  8xcjkzh      |  GadgetInc     | MoreData   |  OtherData2    |  ThisTooStaysText ...


Target table sample structure:


    |  PartNumber  |  Manufacturer  |  SomeData   | SomeMoreData  |  SomeTextData ...
    -------------------------------------------------------------------------------------
    |  1x9kdah     |       1        |    1        |      1        |  ThisStaysText ...
    |  8xcjkzh     |       2        |    2        |      2        |  ThisTooStaysText ...


Master Enumeration Table Structure


    |  FieldName     |  InputText  |  ValueCode |
    ---------------------------------------------
    |  Manufacturer  |  GizmoCorp  |  1         |
    |  Manufacturer  |  GadgetInc  |  2         |
    |  SomeData      |  ThisIsData |  1         |
    |  SomeData      |  MoreData   |  2         |
    |  SomeMoreData  |  OtherData  |  1         |
    |  SomeMoreData  |  OtherData2 |  2         |


We have a means of doing this translation that works and works well; however it's a little on the slow side since all the processing is done in Java via Spring/Hibernate.  My question is:

Is there a way to write a single query that would accomplish all the above translations?  (Note that we have an excellent way of keeping track of our field definitions programmaticly, so generating complex SQL queries on the fly is not an issue).  If it is not possible to do it in a single query, how would I structure queries to iterate over the individual fields and make sure that as the translations happen the data is inserted into the new table remains associated with the correct rows?

Note that it is safe to assume the target table is always empty at the beginning of the process.
Not many people like them, but I would suggest you use a cursor in a stored procedure to loop though the fields and normalise them into the target table (silly ETL process).  

You could probably do better having had separate lookup tables for each type of field, but it depends on you scenario.