Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sync between 2 DB

Author  Topic 

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-07 : 07:14:50
i have 2 different DB.
already create the linked server.

Now i would like to sync the tableA from DB1 and DB2.

if already exist, just leave it.
if some changes on the data, update.
if not exist, insert it.

to update, do i need to fill in all of the column name?
as there are too many column.

kindly advice the best solution for this logic.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-07 : 20:51:21
Rather than doing it via a linked server query, you might want to consider using (transactional) replication. See here: http://msdn.microsoft.com/en-us/library/ms152531.aspx
Go to Top of Page

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-08 : 00:32:06
how can i only update certain rows which has change in DB1 table?
i tried to do update statement but it seems like it update the whole date.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-08 : 09:13:50
If you want to update only a subset of the rows, you have to use a where clause to limit the rows that are affected. If you post the query and some sample data, people on the forum should be able to help. If you need help posting code in a consumable format, see here:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-08 : 22:16:56

does my query make sense here if not exists insert and if exists just update particular rows which has changes and not snyc.
It seems like inserting all and updating all.

if not exists (select col1,col2,col3 from tableA where (select col1,col2,col3 from tableA inner join tableB on tableA.col1=tableB.col1)
insert into tableA
select col1,col2,col3 from tableB
else
update A
set col1=B.col1,col2=B.col2,col3=B.col3
from tableA A
join tableB on A.col1=B.col1
Go to Top of Page

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-11 : 09:39:05
any update?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 09:42:40
quote:
Originally posted by peace


does my query make sense here if not exists insert and if exists just update particular rows which has changes and not snyc.
It seems like inserting all and updating all.

if not exists (select col1,col2,col3 from tableA where (select col1,col2,col3 from tableA inner join tableB on tableA.col1=tableB.col1)
insert into tableA
select col1,col2,col3 from tableB
else
update A
set col1=B.col1,col2=B.col2,col3=B.col3
from tableA A
join tableB on A.col1=B.col1



Use MERGE statement for this type of requirement..
Search online for MERGE in SQL Server

--
Chandu
Go to Top of Page

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-13 : 11:31:23
can i do MERGE for insert statement as below?

when not matched
insert into ...
select * from ...
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-13 : 11:54:27
quote:
Originally posted by peace

can i do MERGE for insert statement as below?

when not matched
insert into ...
select * from ...

No, that is not the syntax of the MERGE statement. You start with two tables - one your target table, and the other your source table - and then do the insert/update/delete on the target table.

Take a look at the tutorial on this page which has good examples: https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/
Go to Top of Page

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-13 : 12:03:12
i get an error message on this:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

What it meant?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 00:25:19
These links may help you to fix the problem...
http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/bc1b37e1-3ed9-4f0a-a323-af69a86cf9ed/

http://experiencing-sql-server-2008.blogspot.in/2008/02/sql-server-2008-merge-dml-statement.html

Basic functionality of MERGE:
http://www.codeproject.com/Articles/37172/Merge-Statement-in-SQL-Server-2008
Go to Top of Page
   

- Advertisement -