I have two different servers, one has a database KSF1, and the other has KSF2. I would like to get a script that copies the data from a table in KSF1 that is not in the same table in KSf2, and then delete the the oldest date data from KSF1.

-- Anonymous, February 02, 2005

Answers

George,

For the sake of demonstration, assume that the table is structured (and named) like the employee table in the pubs database. Then code like this will work:

INSERT INTO SERVER2.KSF2.dbo.employee SELECT e1.* FROM SERVER1.pubs.dbo.employee e1 WHERE NOT EXISTS (SELECT * FROM SERVER2.KSF2.dbo.employee WHERE emp_id = e1.emp_id AND fname = e1.fname AND minit = e1.minit AND lname = e1.lname AND job_id = e1.job_id AND job_lvl = e1.job_lvl AND pub_id = e1.pub_id AND hire_date = e1.hire_date)

DELETE FROM SERVER1.KSF1.dbo.employee WHERE hire_date = (SELECT MIN(hire_date) FROM SERVER1.KSF1.dbo.employee)

Hope this helps,

Eric

-- Anonymous, February 10, 2005