SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sync between 2 DB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

389 Posts

Posted - 04/07/2013 :  07:14:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 04/07/2013 :  20:51:21  Show Profile  Reply with Quote
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

389 Posts

Posted - 04/08/2013 :  00:32:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 04/08/2013 :  09:13:50  Show Profile  Reply with Quote
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

389 Posts

Posted - 04/08/2013 :  22:16:56  Show Profile  Reply with Quote

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

389 Posts

Posted - 04/11/2013 :  09:39:05  Show Profile  Reply with Quote
any update?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/11/2013 :  09:42:40  Show Profile  Reply with Quote
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

389 Posts

Posted - 04/13/2013 :  11:31:23  Show Profile  Reply with Quote
can i do MERGE for insert statement as below?

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

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 04/13/2013 :  11:54:27  Show Profile  Reply with Quote
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

389 Posts

Posted - 04/13/2013 :  12:03:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/15/2013 :  00:25:19  Show Profile  Reply with Quote
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

Edited by - bandi on 04/15/2013 00:27:36
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000