SQLserver 定时跨服务器多表联合更新

---

前言：实际业务中，需要实现定时的跨服务器的多表联合，然后更新一个表的值。

1. 先在A服务器上建立链接服务器，如下
   

然后右击测试链接，测试通过后，在建立存储过程

2.建立存储过程

USE [MSIS]
GO

/****** Object:  StoredProcedure [dbo].[UpdateMSISVisitNumber]    Script Date: 2018/5/16 13:55:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateMSISVisitNumber]
AS
    BEGIN
        --根据身份证号码更新 VisitNumber
        UPDATE  MSIS_Patient
        SET     MSIS_Patient.VisitNumber = D_Paient.PatientIdInDomain ,
                MSIS_Patient.VisitType = D_Paient.DomainId ,
                MSIS_Patient.Remark = GETDATE()
        FROM    [ip地址].EMPI.dbo.PatientInfo AS Patient
                INNER JOIN [ip地址].EMPI.dbo.DomainPatients AS D_Paient ON D_Paient.SeqNo = Patient.SeqNo
                INNER JOIN MSIS.dbo.Patient AS MSIS_Patient ON Patient.IDNumber = MSIS_Patient.National_ID
        WHERE   D_Paient.IsBase = 1
                AND LEN(REPLACE(MSIS_Patient.National_ID, '', ' ')) >= 18
                AND MSIS_Patient.National_ID <> '000000000000000000  '
				AND MSIS_Patient.VisitNumber IS NULL;
	

 --根据EMPI更新 VisitNumber  
        UPDATE  MSIS_Patient
        SET     MSIS_Patient.VisitNumber = D_Paient.PatientIdInDomain ,
                MSIS_Patient.VisitType = D_Paient.DomainId ,
                MSIS_Patient.Remark = GETDATE()
        FROM    [ip地址].EMPI.dbo.EmpiIds AS EmpiIds
                INNER JOIN [ip地址].EMPI.dbo.DomainPatients AS D_Paient ON D_Paient.EmpiId = EmpiIds.EmpiId
                INNER JOIN MSIS.dbo.Patient AS MSIS_Patient ON MSIS_Patient.EMP_ID = EmpiIds.EmpiDisplayId
        WHERE   D_Paient.IsBase = 1
                AND LEN(REPLACE(MSIS_Patient.National_ID, '', ' ')) < 18
                AND MSIS_Patient.EMP_ID IS NOT NULL
				AND MSIS_Patient.VisitNumber IS NULL; 
    END; 
GO

3.最后建立JOB，定时作业就可以了。注意建立完JOB后，先点击JOB名字右击，点击“作业开始步骤”，先测试下JOB 是否有错误。