Data synchronization between SQL Server and Oracle

Data synchronization between SQL Server and Oracle

When it comes to synchronization, it actually relies on the "job" timed scheduling stored procedure to operate on data, add, delete, and modify, all in it, combined with triggers and cursors. For Job Scheduling, I used 5 seconds to run the job once to implement "second-level jobs", so that the basic is relatively fast "synchronization" I am doing synchronization from SQL Server to Oracle. I first created a connection server to Oracle on SQL Server. I used a view to "encapsulate" A table under the linked server. Create view v_ora_publishlastrec As Select * From oracledb .. roadsms. publishlastrec // Oracledb connection server name, roadsms is the tablespace name, publishlastrec is the data table name Then, create the insert, delete, and update triggers on the tables to be synchronized by SQL Server. The script is as follows: -- Note: modiid equals 1 to insert, 2 to delete, and 3 to update. Create trigger trg_publishlastrec_insert on publishlastrec for insert As Insert into DBO. publishlastrec_ SQL (modiid, signalguid, areano, signalnote, areanote, publishroadstatus, publishtime, isexec) Select '1', signalguid, areano, signalnote, areanote, publishroadstatus, publishtime, '0' from inserted Create trigger trg_publishlastrec_update on publishlastrec for update As Insert into DBO. publishlastrec_ SQL (modiid, signalguid, areano, signalnote, areanote, publishroadstatus, publishtime, isexec) Select '3', signalguid, areano, signalnote, areanote, publishroadstatus, publishtime, '0' from inserted Create trigger trg_publishlastrec_delete on publishlastrec for Delete As Insert into DBO. publishlastrec_ SQL (modiid, signalguid, areano, signalnote, areanote, publishroadstatus, publishtime, isexec) Select '2', signalguid, areano, signalnote, areanote, publishroadstatus, publishtime, '0' from deleted All the operations are to insert the data of each phantom table into a table. The preceding uniformly inserted Table is publishlastrec_ SQL, which records the operation identifier to identify whether the record is inserted or deleted, modify it. If modiid is equal to 1, insert is used, delete is used, update is used, and isexec indicates whether the record has been processed. 0 indicates not executed, and 1 indicates executed. Next is the most critical step. The script is as follows: Alter proc pro_publishlastrec_ SQL As Declare @ modiid int Declare @ signalguid int Declare @ areano numeric (1, 0) Declare @ signalnote varchar (50) Declare @ areanote varchar (50) Declare @ publishroadstatus varchar (20) Declare @ publishtime varchar (50) If not exists (select * From publishlastrec_ SQL where isexec = 0) Begin Truncate table publishlastrec_ SQL Return End Declare cur_ SQL cursor Select modiid, signalguid, areano, signalnote, areanote, publishroadstatus, publishtime from Publishlastrec_ SQL where isexec = 0 order by [ID] -- isexec 0 is not executed, 1 is executed Open cur_ SQL Fetch next from cur_ SQL into @ modiid, @ signalguid, @ areano, @ signalnote, @ areanote, @ publishroadstatus, @ publishtime While @ fetch_status = 0 Begin If (@ modiid = 1) -- insert Begin Insert into v_ora_publishlastrec (signalguid, areano, signalnote, areanote, publishroadstatus, publishtime) Values (@ signalguid, @ areano, @ signalnote, @ areanote, @ publishroadstatus, @ publishtime) End If (@ modiid = 2) -- delete Begin Delete from v_ora_publishlastrec where Signalguid = @ signalguid And Areano = @ areano End If (@ modiid = 3) -- modify Begin Update v_ora_publishlastrec Set Signalnote = @ signalnote, areanote = @ areanote, publishroadstatus = @ publishroadstatus , Publishtime = @ publishtime Where Signalguid = @ signalguid And Areano = @ areano End Update publishlastrec_ SQL Set isexec = 1 Where current of cur_ SQL Fetch next from cur_ SQL into @ modiid, @ signalguid, @ areano, @ signalnote, @ areanote, @ publishroadstatus, @ publishtime End Deallocate cur_ SQL The stored procedure uses a cursor to extract publishlastrec_ SQL records row by row and identifies different data operations based on the modiid. After the record is processed, the isexec field is updated to 1. Finally, we call the Stored Procedure job. We first create a job that runs once a minute, and then write it in the "Step" script as follows: Declare @ DT datetime Set @ dt = dateadd (minute,-1, getdate ()) While @ dt <getdate () Begin Exec pro_publishlastrec_ SQL -- here, pro_publishlastrec_ SQL is the Stored Procedure for executing your job. Waitfor delay '00: 00: 05 '-- wait for 5 seconds. Set it as needed. End in this way, we can execute the stored procedure once every 5 seconds to synchronize the stored procedure in 5 seconds, ^_^, please enjoy!