OK Experts, I need your help again.
I am new to MS SQL. What is the best way to sncronize tables? I have 1 table(Employee) that has a field called EmployeeNumber as it's primary key. I have another table(Collect) that has the same field but a different primary key. I would like to set it up so that every time a new record gets added to the Employee table The EmployeeNumber gets added to the collect table as well.
I have a procces that runs monthly it delets all of the records in the employee table then adds a whole new record set. When this happens, I would like it to add new records to collect but not delete any.
I need one more thing to happen but I can continue that in a new question If you experts think that is best and I don't know how difficult this all is. I need SQL server to periodically compare the two tables and remove any records that exist in the collect table that do not exist in the Employee Table.
If I am not explaining this well please ask questions. I know what I want, but only a very vague idea of how to do it.