Solved

Synchronizing two tables using a stored procedure, only updating and adding rows where values do not match

Posted on 2014-04-13
11
1,216 Views
Last Modified: 2014-05-08
Hi All,

I've got two tables with identical structure. I'm trying to create a synchronize procedure that will be triggered by a scheduled event at a given time every day.
truncate table [localserver].[DBName].dbo.linktest
go
insert into [localserver].[DBName].dbo.linktest
(
ID,Name
)
select ID,Name
from [remoteserver].DBName.dbo.linktest
with (nolock)

Open in new window

Instead of inserting every single row on every execution, like in my scrpt, is it possible to only insert new rows on local server ?

This would greatly increase the execution speed, since it doesn't have to insert all the 4000 rows when maybe only 20 new rows.

Please advise how to modify above script to insert only new rows (append) on every execution instead of all 4000 rows.
0
Comment
Question by:sqldba2013
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 63 total points
ID: 39998324
insert into [localserver].[DBName].dbo.linktest
(
ID,Name
)
select ID,Name
from [remoteserver].DBName.dbo.linktest
with (nolock)
where id not in ( select ID from [localserver].DBName.dbo.linktest )
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39998331
To Update

Update
Set L.Name = R.Name
From [localserver].[DBName].dbo.linktest L
inner join [remoteserver].DBName.dbo.linktest R on L.id=R.id
where LName <> R.Name
0
 

Author Comment

by:sqldba2013
ID: 39998676
for the performance prospective, which option is best that is update or insert?
0
 
LVL 1

Assisted Solution

by:pven13
pven13 earned 63 total points
ID: 39998707
You can use the MERGE-statement to handle both inserts and updates:

MERGE [localserver].[DBName].dbo.LinkTest AS L
USING [remoteserver].[DBName].dbo.LinkTest AS R
ON L.id = R.id
WHEN MATCHED THEN
	UPDATE SET L.name = R.name
WHEN NOT MATCHED THEN
	INSERT (id, name)
	VALUES (R.id, R.name);

Open in new window


If you don't need the updates, you can skip the "when matched" part.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 124 total points
ID: 39999565
For performance, you need to send the row ids/keys to a temporary table on the remote server, so that the join is completely on the remote server.  Otherwise, SQL is very likely to copy the entire remote table to the local server to do the join.

Therefore, create a table on [remoteserver] to hold the key values from [localserver], then DELETE and load that table (you can't TRUNCATE a remote table).

--run on [remoteserver]
--CREATE TABLE /*[remoteserver].*/DBName.dbo.linktest_keys ( ID NOT NULL );

DELETE FROM TABLE [remoteserver].DBName.dbo.linktest_keys

INSERT INTO [remoteserver].DBName.dbo.linktest_keys ( ID )
SELECT ID
FROM [localserver].DBName.dbo.linktest WITH (NOLOCK)

INSERT INTO [localserver].DBName.dbo.linktest
SELECT l.ID, l.Name
FROM [remoteserver].DBName.dbo.linktest l WITH (NOLOCK)
LEFT OUTER JOIN [remoteserver].DBName.dbo.linktest_keys lk ON
    lk.ID = l.ID
WHERE
    lk.ID IS NULL


As to changes, I'd use some type of datetime column or other flag on the row so you could tell if an UPDATE had been run on that row since the last time you sync'd the tables.  Anything else would likely be a lot more overhead.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Accepted Solution

by:
sqldba2013 earned 0 total points
ID: 40000767
we don't have access on remote server and we have only read permissions on remote server.

Can we perform above steps on local server instead of remote?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40001644
You can do a local join to the remote server table, using LEFT JOIN as demonstrated above.

But the performance will not get better, since all rows will still have to be copied from the remote server to the local server.
0
 
LVL 1

Expert Comment

by:pven13
ID: 40006078
Why don't you run a simple test and see how well the different solutions perform? No one can predict that since we don't know the specs of your network connections.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 124 total points
ID: 40006852
Regardless of the current network connections and existing load (or lack of it), it's a poor practice to force SQL to copy huge tables to another server to do a join when you can copy a vastly smaller select list yourself to the other server.
0
 
LVL 1

Expert Comment

by:pven13
ID: 40008223
I agree. But the author mentioned 4000 rows and 20 new rows. That's not what I call "huge tables"... He also wrote he only has read permission on the remote server.

Besides, I would like to know if sqldba2013 is going to do something with our suggestions.
0
 

Author Closing Comment

by:sqldba2013
ID: 40049823
the above solutions resolved my issue.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now