Hi Jim,
We are using something similar and MERGE is just perfect for this as mentioned. In our solution we are creating a work table and load the data in it. This table can have referential constraints to avoid wrong data come in (since you already have that you can skip this). In the next phase you can use the MERGE to load the data to your final table in one shot. Lastly you may want to clear your table with a truncate or so.
Regarding the MERGE you can use like this:
MERGE tbl_EmployeRecord AS destination
USING (SELECT EMPID,EMPNAME,DEPTNAME FROM tbl_Department D INNER JOIN tbl_Employee E ON D.DEPARTMENTID=E.DEPARTMENTID) AS source
ON destination.EMPID = source.EMPID
WHEN NOT MATCHED THEN
INSERT (EMPID, EMPNAME, DEPTNAME)
VALUES (source.EMPID, source.EMPNAME, source.DEPTNAME)
WHEN MATCHED THEN
UPDATE ...
;
I have tried on my sample DB and worked perfectly, hope this helps :-). Update is not implemented, but you can extend it based on your requirements.
Regards,
Tibor