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,301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

738 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