Author |
Topic |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-04-23 : 09:25:45
|
I try to insert a new record into table B but it seems like it doesn't insert the record. It only update. Anythg wrong with my query?MERGE dbo.tableA AS BUSING [...].[...].[...].tableB AS A with (nolock)ON A.col1=B.col1AND A.col2=B.col2WHEN MATCHED THEN UPDATESET B.col1= A.col1,B.col2=A.col2,B.col3=A.col3WHEN NOT MATCHED THENINSERT(col1,col2,col3)VALUES(A.col1,A.col2,A.col3); |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-23 : 10:15:46
|
well it is confusing to look at because you aliased tableA as B and tableB as A.So your update is updating TableA (B) but your insert is inserting columns from TableB (A) from TableB (itself).Be One with the OptimizerTG |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-04-23 : 10:28:38
|
what should be the correct query |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-04-23 : 10:47:39
|
i get this error:The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-23 : 10:55:21
|
quote: Originally posted by peace i get this error:The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.
It is just one of those restrictions on MERGE statement http://technet.microsoft.com/en-us/library/bb510625.aspxSo you will have to do it the old fashioned way as two separate statements. |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-04-23 : 11:02:10
|
i tried to insert and update in separate statements.but it seems like it insert the whole record. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-23 : 13:45:53
|
I think you're going to need to post the actual code for us to help you. Also post the specific problem when you run the code (you will post). If it is an error then post the exact error.Be One with the OptimizerTG |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-04-23 : 22:00:32
|
i tried this but i get this error:The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.merge to linked server which is tableAMERGE [...].[...].[...].tableA AS BUSING tableB AS A with (nolock)ON A.col1=B.col1AND A.col2=B.col2WHEN MATCHED THEN UPDATESET B.col1= A.col1,B.col2=A.col2,B.col3=A.col3WHEN NOT MATCHED THENINSERT(col1,col2,col3)VALUES(A.col1,A.col2,A.col3); |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-23 : 23:24:58
|
what is this?quote: [...].[...].[...].
did you just remove the [<server>].[<database>].[<schema>]. names so we couldn't see them or is that your actual code?In either case, as the error message and James both say, you can't perform a merge across two database servers."remote" means a table on a different server. Is that what you're trying to do?The best practice method for keeping a remote table in sync with a local table is with sql replication.Be One with the OptimizerTG |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-04-24 : 00:47:35
|
It is linked server.Now im getting it correct already.Just add inexec ('MERGE STATEMENT') at LINKSERVERNow i try to update new record.But it seems like it insert instead of doing the update.My merge query still maintain the same as above. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-24 : 01:26:46
|
Be sure it satisfies the condition (A.col1=B.col1 AND A.col2=B.col2)...--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-24 : 01:33:27
|
quote: Originally posted by peace It is linked server.Now im getting it correct already.Just add inexec ('MERGE STATEMENT') at LINKSERVERNow i try to update new record.But it seems like it insert instead of doing the update.My merge query still maintain the same as above.
then the issue might be in columns you're defined for checking. are you sure you've included correct set of columns which will uniquely identify a new row (pk combination)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|