Hi Experts,

I'm looking for thoughts on the best way to handle the following situation.  I'm doing a one-time load of data into two tables (for simplicity's sake).   The first one holds a person's current name and the date it was introduced to the system. 

create table NAME_PRIMARY (PR_ID int identity primary key, FNAME nvarchar(50), LNAME nvarchar(50), RecDate Date)

Then, there's a table that contains the person's name history.

create table NAME_HISTORY (ID int identity, PR_ID int, FNAME nvarchar(50), LNAME nvarchar(50), RecDate date)

There's a trigger on NAME_PRIMARY that automatically inserts info into into NAME_HISTORY when a record in NAME_PRIMARY is updated.   So if someone has married and changed her name, the married name goes into NAME_PRIMARY and the maiden name goes into NAME_HISTORY via a trigger (on UPDATE).

My task is a one time load of data into our system from a flat source table that may contain multiple records for a given person.  Assuming there's a unique person identifier in the source table and a date_of_entry field, what is the *best* way to load the target tables?  I can think of a couple options, but I'd like to hear from anyone with experience.  I can't be the first one to face this challenge.  :)

1)  Load most recent into NAME_PRIMARY and then try to load the older records, letting the trigger do the work of inserting the older records into NAME_HISTORY.

2)  Write a query to sort things out and insert into both NAME_PRIMARY and NAME_HISTORY.  I think this would be faster but the only way I can think to do it is with a cursor or some kind of loop, and I don't know if that's a good idea or not.

What do you think?  THanks in advance.

• Edited by SSIS struggler 5 hours 57 minutes ago

i would go with option 2.

Disable trigger, obviously you have to be sure no other activity is happening on the tables at the time, load both tables, through SSIS/T-SQL (your preference) re-enable the trigger when done.

That way you control entire dataflow to both tables through the entire process :)

let me know if that helps