SQL 2008 Merge - SQL Server Forums

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

SQL 2008 Merge

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 B
USING [...].[...].[...].tableB AS A with (nolock)

ON A.col1=B.col1
AND A.col2=B.col2

WHEN MATCHED THEN

UPDATE
SET B.col1= A.col1,
B.col2=A.col2,
B.col3=A.col3

WHEN NOT MATCHED THEN

INSERT(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 Optimizer
TG

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.aspx

So 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 Optimizer
TG

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 tableA

MERGE [...].[...].[...].tableA AS B
USING tableB AS A with (nolock)

ON A.col1=B.col1
AND A.col2=B.col2

WHEN MATCHED THEN

UPDATE
SET B.col1= A.col1,
B.col2=A.col2,
B.col3=A.col3

WHEN NOT MATCHED THEN

INSERT(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 Optimizer
TG

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 in

exec ('MERGE STATEMENT') at LINKSERVER

Now 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 in

exec ('MERGE STATEMENT') at LINKSERVER

Now 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources