All,

I am trying to use INSERT INTO to transfer "non-normalized" data from my staging tables to my properly normalized tables. In the non-normalized structure the parent, child, and grand-child all have a field called CUSTID that links the grand-child directly to the parent(grand-parent).

Staging Tables: non-normalized   All have CUSTNUM field, and no unique Identifier  
The Transformers and Visual_Inspection tables have both a CUSTNUM and TRANSNUM.
Customer - Parent
     Transformers -      Child
     Visual_Inspection - Child
                                                        
Live Tables: Normalized  All have integer unique Identifier
Contacts - Parent
    Equipment -          Child
         Inspections -   Grand-Child.

In my normalized table the CUSTNUM is stored in the Contacts table ONLY and the equipment is linked by the unique ID of the Parent, Likewise, the EQUIPNUM is stored in the Equipment table only, and the Inspections table is linked to it by the unique equipment ID.

I need to be able to transfer data from my staging tables to my live tables on a regular basis, as this will be a daily update procedure from a VFP Desktop app to my SQL Server based Web app.

I'm trying to use INSERT INTO as follows:

insert into lab.inspection(
      lab.insepction.id_equipment,
      lab.inspection.dateinsp,
      lab.inspection.optemp,
      .... more fields continue
select
       "some way to get the unique equip ID based on the CUSTNUM and EQUIPNUM in src.transformers"
       src.transformers.datinp,
       src.transformers.optemp,
       .....more fields continue
from src.visual_inspection

I have written a script that includes adding additional fields to my inspection table for "oldcustnum" and "oldequipnum" and the updating the IDs based on those values and then dropping the columns.

But isn't there a better way than actually altering the table's structure constantly?