>>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.visual_inspection.datinp,
>>       src.visual_inspection.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?
>
>In SQL Server 2008 you will need to look into MERGE command.


Naomi,

Can you give me some hints here?

I have studied the MERGE command and looked at several examples all over the web, and I still have no clue as to how I am going to use the MERGE command to allow me to get the proper id_equipment into the inspection table based on what is contained in my src.visual_inspection table.

The MERGE command looks like a very useful command when it comes to updating, inserting, and deleting for tables with a normal Parent - Child relationship. But remember, I'm trying to import data into a Grand-Child table without having to add columns to contain the old data that related them and then drop the columns after use.

NOTE: I just noticed that my initial request should say "src.visual_inspection" not "src.transformers in the select portion of the insert statement.

Thank you for your always helpful help. : )