步步为营99-不同数据库数据实时同步

50) NOT NULL, [CorporateName] [nvarchar](50) NULL, [Status] [nvarchar](20) NULL, [UpdateTime] [datetime] NULL, [F1] [nvarchar](200) NULL, [F2] [nvarchar](200) NULL, [F3] [nvarchar](200) NULL, [F4] [nvarchar](200) NULL, [F5] [nvarchar](200) NULL, CONSTRAINT [PK_Temp_MDM_Corporate] PRIMARY KEY CLUSTERED ( [CorporateCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Z_MDM_Corporate]( [CorporateCode] [varchar](50) NOT NULL, [CorporateName] [nvarchar](50) NULL, [Status] [nvarchar](20) NULL, [UpdateTime] [datetime] NULL, [F1] [nvarchar](200) NULL, [F2] [nvarchar](200) NULL, [F3] [nvarchar](200) NULL, [F4] [nvarchar](200) NULL, [F5] [nvarchar](200) NULL, CONSTRAINT [PK_Z_MDM_Corporate] PRIMARY KEY CLUSTERED ( [CorporateCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO View Code

      1.2 创建存储过程

CREATE PROCEDURE sp_UpdateZ_MDM_Corporate
 
AS
BEGIN
       MERGE INTO Z_MDM_Corporate AS Z
       USING Temp_MDM_Corporate AS T
       ON Z.CorporateCode=T.CorporateCode
       WHEN MATCHED
       THEN UPDATE SET Z.CorporateName=T.CorporateName, Z.Status = T.Status,Z.UpdateTime=t.UpdateTime
       WHEN NOT MATCHED
       THEN INSERT  VALUES (T.CorporateCode,T.CorporateName,T.Status,T.UpdateTime,T.F1,T.F2,T.F3,T.F4,T.F5);
 
    END
 

View Code

　　1.3 说到这随便提一下,我们可以在数据库中创建一个job,每个一段时间跑一下存储过程.

　　1.3.1

1.3.2

1.3.3

2:使用window服务,从项目主数据中获取数据插入到临时表中Temp_MDM_Corporate.

步步为营99-不同数据库数据实时同步

标签：中间   clu   null   dex   rate   play   解决   compute   row