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,347 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
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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