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 |
|
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. |
|
|
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 |
|
|
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 tableAselect col1,col2,col3 from tableBelseupdate Aset col1=B.col1,col2=B.col2,col3=B.col3from tableA Ajoin tableB on A.col1=B.col1 |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-04-11 : 09:39:05
|
any update? |
|
|
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 tableAselect col1,col2,col3 from tableBelseupdate Aset col1=B.col1,col2=B.col2,col3=B.col3from tableA Ajoin tableB on A.col1=B.col1
Use MERGE statement for this type of requirement..Search online for MERGE in SQL Server--Chandu |
|
|
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 matchedinsert into ...select * from ... |
|
|
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 matchedinsert 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/ |
|
|
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? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|