Sql server database Links

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

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



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,


-- Anonymous, February 10, 2005

Moderation questions? read the FAQ