appending data with sql

[Thai]

Hello, is there a way in sql to insert data into a "Master" table from a "Working" table and only insert the fields that are in the "Working" table which exist in the "Master" table without looping through the fields and building a statement?

Example:
Master Table Fields: PartNum, Desc, MFR, MFRNum
Working Table Fields: PartNum, Desc, Other Desc, MFR, MFRNum

Is there a way to insert all of the fields in working table into master table where the field working table exists in master? I have to loop through both tables and do a check against the fields to build an array of matching fields currently, but I was just wondering if theres an SQL function that will do it.

Thanks!
Thai

[JHausmann]

This sql will copy in the records that are missing from the one table to the other (insert)

insert into master select * from working where unique_key not in(select unique_key from from master)


But it really sounds like you want to update matching data:

update master set a.field1=b.field1, a.field2=b.field2 from working a, master b where a.unique_key=b.unique_key

This works on SQL server (the update..from construct is a T-SQL extension to SQL) but I do not know about access.

[Thai]

I am working with SQL Server.. so this should update the append data from one table to another where the field NAMES match?

thanks,
Thai

[JHausmann]

Yes. Just make sure that your where clause ties the records between the two tables on a one to one (record) basis.