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

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

阅读量：5304 次

发布时间：2019-06-14

本文共 5082 字，大约阅读时间需要 16 分钟。

说明:

项目主数据中间表同步

项目主数据现在做了3张新的中间表，我们的任务就是做个windows服务从那3张中间表拉取部分字段数据插入我们老的中间表

解决方案:

先从代码里连接中间表数据库，利用c# sqlserver类里的批量插入方法统一插入到本地的临时表，然后在存储过程里把临时表数据处理到目的表。

1: 先写一个存储过程:实现临时表[dbo].[Temp_MDM_Corporate]和中间表[dbo].[Z_MDM_Corporate]比对.

　　如果中间表中的数据和临时表一致,不进行操作;

　　如果不一致:中间表中没有数据,则插入,有则更新

　　1.1 创建数据表　　

CREATE TABLE [dbo].[Temp_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_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 ASBEGIN       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:使用数据库连接+job定时执行数据库同步,同时根据modify做增量更新

.2.1 为连接起一个名字

2.2 设置要连接远程机器的登录名和密码

3 添加job

ALTER PROCEDURE [dbo].[sp_InsertTemp_MDM] @IsGlobalOrAdd int --0表示全量更新,1表示增量更新ASBEGIN    delete from Temp_MDM_Corporate     if(@IsGlobalOrAdd=0)     begin        --向"法人公司临时表"中添加数据        insert into [Temp_MDM_Corporate]  (CorporateCode, CorporateName, Status, UpdateTime)        SELECT FCompanyCode,FCompanyName,Status,ModifyDate FROM [YANGO].[yg_mds_middle].[dbo].[MDS_BPM_Corporate]        --向"地块临时表"中添加数据        insert into [Temp_MDM_Project_Parcel]  (ProjectCode, ProjectName, ParcelCode, ParcelName, LandUse, CorporateName, CorporateCode, LandUseCode, Status, UpdateTime)        SELECT ProjectCode,ProjectName,ParcelCode,ParcelPhaseName,LandUse,FCompanyName,FCompanyCode,LandUseCode,Status,ModifyDate FROM [YANGO].[yg_mds_middle].[dbo].[MDS_BPM_Parcel]     end     else      begin         --向"法人公司临时表"中添加数据        insert into [Temp_MDM_Corporate]  (CorporateCode, CorporateName, Status, UpdateTime)        SELECT FCompanyCode,FCompanyName,Status,ModifyDate FROM [YANGO].[yg_mds_middle].[dbo].[MDS_BPM_Corporate] yc        WHERE yc.[ModifyDate]  > (select max(UpdateTime) from Z_MDM_Corporate)        --向"地块临时表"中添加数据        insert into [Temp_MDM_Project_Parcel]  (ProjectCode, ProjectName, ParcelCode, ParcelName, LandUse, CorporateName, CorporateCode, LandUseCode, Status, UpdateTime)        SELECT ProjectCode,ProjectName,ParcelCode,ParcelPhaseName,LandUse,FCompanyName,FCompanyCode,LandUseCode,Status,ModifyDate FROM [YANGO].[yg_mds_middle].[dbo].[MDS_BPM_Parcel] yp        WHERE yp.[ModifyDate]  > (select max(UpdateTime) from Z_MDM_Project_Parcel)     end END

sp_InsertTemp_MDM

ALTER PROCEDURE [dbo].[sp_UpdateZ_MDM] ASBEGIN       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 = (case when T.Status = 1 then '修改' else '删除' end),Z.UpdateTime=t.UpdateTime       WHEN NOT MATCHED --没有匹配到状态为"新增"       THEN INSERT  VALUES (T.CorporateCode,T.CorporateName,'新增',T.UpdateTime,T.F1,T.F2,T.F3,T.F4,T.F5);         --匹配"中间表_地块信息"       MERGE INTO Z_MDM_Project_Parcel AS ZPP       USING Temp_MDM_Project_Parcel AS TPP       ON ZPP.ParcelCode=TPP.ParcelCode       WHEN MATCHED        THEN UPDATE SET  ZPP.ProjectCode=TPP.ProjectCode,ZPP.ProjectName=TPP.ProjectName, ZPP.FinancialSystemCode=TPP.FinancialSystemCode, ZPP.ParcelCode=TPP.ParcelCode, ZPP.ParcelName=TPP.ParcelName, ZPP.LandUse=TPP.LandUse, ZPP.CorporateName=TPP.CorporateName, ZPP.CorporateCode=TPP.CorporateCode, ZPP.LandUseCode=TPP.LandUseCode, ZPP.Status = (case when TPP.Status = 1 then '修改' else '删除' end),ZPP.UpdateTime=TPP.UpdateTime       WHEN NOT MATCHED --没有匹配到状态为"新增"       THEN INSERT  VALUES (TPP.ProjectCode,TPP.ProjectName, TPP.ParcelCode, TPP.ParcelName,TPP.LandUse, TPP.CorporateName,TPP.FinancialSystemCode,TPP.CorporateCode,TPP.LandUseCode,'新增',TPP.UpdateTime,TPP.F1,TPP.F2,TPP.F3,TPP.F4,TPP.F5);    END

sp_UpdateZ_MDM

转载于:https://www.cnblogs.com/YK2012/p/8012169.html

你可能感兴趣的文章