Merge Statement over a linked server

Question

Post reply

Merge Statement over a linked server

cgauntlett

Old Hand

Points: 341
More actions
November 24, 2012 at 3:42 pm

#285090

Does anyone know a way of running a MERGE statement across 2 tables over a linked server?
Getting the message "The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables."

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 1

Try executing the whole statement at destination db:

EXEC('MERGE ...') AT MYDBLINKNAME

What is remote server? Sql server, oracle or something else?

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

cgauntlett Old Hand Points: 341 More actions · Answer 2

Thanks for the suggestion, i'll give it a try.

This is SQL 2008R2 setup I'm trying to run it on.

cgauntlett Old Hand Points: 341 More actions · Answer 3

Tried it yesterday and it worked perfectly. Thanks again.

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 4

You're welcome!

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

girl_bj SSCommitted Points: 1731 More actions · Answer 5

girl_bj

SSCommitted

Points: 1731

April 23, 2013 at 8:51 am

#1609111

how does the query works?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

girl_bj0619 (4/23/2013)
how does the query works?

Execute At [linked_server]

causes the statement to be executed on the linked server (not on the server running the EXECUTE statement). Is that what you mean?

--Edit: fix typo

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

girl_bj SSCommitted Points: 1731 More actions · Answer 7

i meant where to apply it?

here..?

merge ...

using....

Phil Parkin SSC Guru Points: 246985 More actions · Answer 8

girl_bj0619 (4/23/2013)
i meant where to apply it?
here..?
merge ...
using....

Can you take a bit more time to write out what you are trying to do please, because I do not understand your question?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

I think you are asking where do you run the MERGE???

EXEC('Your Entire MERGE Statement goes here') at LinkServer

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

girl_bj SSCommitted Points: 1731 More actions · Answer 10

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);

girl_bj SSCommitted Points: 1731 More actions · Answer 11

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);

Phil Parkin SSC Guru Points: 246985 More actions · Answer 12

Are your source and target tables on different servers?

The MERGE statement won't work in this scenario - you need both the tables to be on the same SQL instance, I believe.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eric M Russell SSC Guru Points: 125562 More actions · Answer 13

girl_bj0619 (4/23/2013)
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);

You are referencing your target table (tableA) using 4 part naming convention (server.db.schema.object), so it probably looks to SQL Server as if you're attempting to MERGE into a remote table. Insure that you're running the MERGE statement locally on the same server as your target table, and re-write your reference to target table using 3 or 2 part naming convention like this:

MERGE [db].[schema].

AS B

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho