Complete sql synchronization using query

Question

I would like to sync data from offline database to online database. Currently I am performing it using query with some conditions. But data that are edited in offline database after syncing will not be synced again to online in next sync. Is there any easy method to update edited data in online db. My current query is as like below :

INSERT INTO OPENQUERY([103.21.58.192], 'SELECT * FROM [amurajbg_ss].[dbo].[AccLedgers]') 
    SELECT * 
    FROM OPENQUERY([USER-PC\SQLEXPRESS],
                   'DECLARE @LastSyncDate AS DATETIME   
                    DECLARE @CurrentSyncDate AS DATETIME 
                    SELECT @CurrentSyncDate = GETDATE() 
                    SELECT @LastSyncDate = LastSyncDate 
                    FROM [CrushMate].[dbo].[Sync] 
                    SELECT * FROM [CrushMate].[dbo].[AccLedgers] 
                    WHERE CreatedDate BETWEEN @LastSyncDate AND @CurrentSyncDate 
                      AND Status=''True'' ')

My online database server doesn't allow me to perform replication, because I have no permissions to do that. Only way is through query like this.

Thank you

2 `OPENQUERY` ? Where is this query executing ? Looks like there are 3 hosts involved here — Squirrel, Sep 25 '18 at 05:58
query is being executed from offline db. I am not so good in sql. If you can suggest improvements to this, please help. It is being done through linked server. I tried ordinary pushing query, but this is less time consuming. — faheemKurikkal, Sep 25 '18 at 06:02
This is pretty clear. Ignoring the confusion around use of OpenQuery and remote servers, you are basing your test on adddate, instead of lastupdatedate. You need the latter to be able to use this pattern. — TomC, Sep 25 '18 at 06:16
Is there any query model to update all entries in a table which is edited in offline db. that is, want to select rows which is modified and to update in online db — faheemKurikkal, Sep 25 '18 at 07:00
I just need complete syncing. Replication is not supported. Thank you — faheemKurikkal, Sep 25 '18 at 07:06

Squirrel · Answer 1 · 2018-09-25T07:09:25.723

But data that are edited in offline database after syncing will not be synced again to online in next sync

You need another column the offline database that identify that the data is being edited. Typically you can use a UpdatedDate column. This UpdatedDate will be updated with current date & time whenever the data is being edited. If you application can handle this, it will be good. If not, you can use a AFTER UPDATE trigger to handle this.

Then your sync query will need to check the UpdatedDate as well.

DECLARE @LastSyncDate AS DATETIME   
DECLARE @CurrentSyncDate AS DATETIME 

SELECT @CurrentSyncDate = GETDATE() 

SELECT @LastSyncDate = LastSyncDate 
FROM   [CrushMate].[dbo].[Sync]

-- the INSERT
INSERT INTO OPENQUERY([103.21.58.192], 'SELECT * FROM [amurajbg_ss].[dbo].[AccLedgers]') 
SELECT * 
FROM   [CrushMate].[dbo].[AccLedgers] 
WHERE  CreatedDate BETWEEN @LastSyncDate AND @CurrentSyncDate 
AND    Status = 'True'

for update, it will be easier to use Linked Server. You may refer to Here for more information.

UPDATE R
SET    . . . .
FROM   [CrushMate].[dbo].[AccLedgers] A
       INNER JOIN [RemoteServer].[amurajbg_ss].[dbo].[AccLedgers] R
       ON   A.pk_col = R.pk_col
WHERE  A.UpdatedDate   BETWEEN @LastSyncDate AND @CurrentSyncDate

when we insert with this query will cause for duplication. I want to update the edited data in online database also. I am a beginner, so help with code — faheemKurikkal, Sep 25 '18 at 06:57

Complete sql synchronization using query

1 Answers1