Step by step 99-real-time synchronization of different database data

Description

Project Master Data Intermediate table synchronization
Project master data now makes 3 new intermediate tables, our task is to do a Windows service from those 3 intermediate tables pull part of the field data into our old intermediate table

Solution:

First, connect the intermediate table database from the code, use the Bulk Insert method in the C # SQL Server class to insert into the local temporary table uniformly, and then process the temporary table into the destination table in the stored procedure.

1: Write a stored procedure first: implement temporal table [dbo]. [Temp_mdm_corporate] and intermediate table [dbo]. [Z_mdm_corporate] the comparison.

If the data in the intermediate table is consistent with the temporary table, the operation is not performed;

If inconsistent: no data in the intermediate table, insert, update

1.1 Creating a data table

CREATE TABLE [dbo]. [Temp_mdm_corporate] ([Corporatecode] [varchar] ( -) not NULL, [corporatename] [nvarchar] ( -) NULL, [Status] [nvarchar] ( -) NULL, [UpdateTime] [datetime] NULL, [F1] [nvarchar] ( $) NULL, [F2] [nvarchar] ( $) NULL, [F3] [nvarchar] ( $) NULL, [F4] [nvarchar] ( $) NULL, [F5] [nvarchar] ( $) 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] ( -) not NULL, [corporatename] [nvarchar] ( -) NULL, [Status] [nvarchar] ( -) NULL, [UpdateTime] [datetime] NULL, [F1] [nvarchar] ( $) NULL, [F2] [nvarchar] ( $) NULL, [F3] [nvarchar] ( $) NULL, [F4] [nvarchar] ( $) NULL, [F5] [nvarchar] ( $) 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 Creating a stored procedure

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 isn't       matched then       INSERT  VALUES (t.corporatecode,t.corporatename,t.status,t.updatetime , t.f1,t.f2,t.f3,t.f4,t.f5);     

View Code

1.3 Speaking of which, we can create a job in the database and run the stored procedure for a period of time.

1.3.1

1.3.2

1.3.3

2: Use the window service to get data from the project master data into the temporary table temp_mdm_corporate.

Step by step 99-real-time synchronization of different database data