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.
Author |
Topic |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-03-25 : 10:20:54
|
how can i insert new record from DB 1 to DB 2.if already exist, just need to update to sync it.i tried this but it doesn't work.if exists (select distinct a.* from tableA a inner join tableB b on a.col1=b.col1 )update tableAset a.col1=b.col1,a.col2=b.col2from tableB binner join tableA aon a.col1=b.col1 elseINSERT INTO tableA (col1,col2)select * from tableBwhere not exists (select * from tableA) |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-25 : 10:55:29
|
You should let us know why it "doesn't work" - ie: error, incorrect results, etc.At first glance I'd guess the reason it "doesn't work" your final insert where not exists statement. If there are ANY rows in tableA then the where clause will never be satisfied.Sql Server 2008 and later offers a MERGE statement. That may be better for you.Be One with the OptimizerTG |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-25 : 11:21:50
|
it should beif exists (select a.* from tableA a inner join tableB b on a.col1=b.col1 )update tableAset a.col1=b.col1,a.col2=b.col2from tableB binner join tableA aon a.col1=b.col1 elseINSERT INTO tableA (col1,col2)select * from tableB bwhere not exists (select * from tableA where col1 = b.col1) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-03-25 : 11:26:34
|
Oh thanks. I missed out here:.....elseINSERT INTO tableA (col1,col2)select * from tableB bwhere not exists (select * from tableA where col1 = b.col1) |
|
|
|
|
|