January 27, 2010 at 2:51 pm
I have been asked to write a stored proc which ensures I have identical employee tables on 2 linked servers. Each night I would like to schedule a job which executes a Sproc to keep the tables identical in content. (I know the basics of stored procs but not enough to write something like this!)
This is a simple pseudo code of what I would like the sproc to do. The employeeMaster table on Server A is the master table and I would like the sproc to update the employeeMaster table on Server B as follows:
If the Empoyee.dbo.employeeMaster table does not exist on Server B, simply do a select into ServerB.Empoyee.dbo.employeeMaster table from ServerA.Empoyee.dbo.employeeMaster table.
If the table exists:
If the row exists (A.empId = B.empId)
Update ServerB.Empoyee.dbo.employeeMaster B from ServerA.Empoyee.dbo.employeeMaster A hireDate, department, salary
If the row does not exist insert the row into Server B employeeMaster from Server A employeeMaster.
Finally if empId's exist in ServerB.Empoyee.dbo.employeeMaster that do not exist in ServerA.Empoyee.dbo.employeeMaster delete them from ServerB.Empoyee.dbo.employeeMaster.
Can anyone lead me to a Sql Server stored proc that does something similar to this so I can apply it for my needs? Thanks, JT.
January 28, 2010 at 6:33 am
Something like this:
CREATE PROCEDURE sync_employee_table
AS
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM linkedserver.DATABASE.sys.tables WHERE NAME = 'Employee')
BEGIN
UPDATE L
SET column_list = A.column_list
FROM
linkedserver.DATABASE.SCHEMA.employee L JOIN
SCHEMA.employee E ON
E.empid = L.empid
INSERT INTO linkedserver.DATABASE.SCHEMA.employee
(
columns
)
SELECT
E.COLUMNS
FROM
SCHEMA.employee E LEFT JOIN
linkedserver.DATABASE.SCHEMA.employee L ON
E.empid = L.empid
WHERE
L.empid IS NULL
DELETE L
FROM
linkedserver.DATABASE.SCHEMA.employee L LEFT JOIN
SCHEMA.employee E ON
E.empid = L.empid
WHERE
E.empid IS NULL
END
ELSE
Begin
SELECT
*
INTO
linkedserver.DATABASE.SCHEMA.employee
FROM
schema.employee
END
A better solution would be to create a Service Broker application that queues employee inserts/updates/deletes and then you can asynchronously apply the changes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply