| Author |
 Topic  |
|
|
rmg1
Posting Yak Master
245 Posts |
 Posted - 02/06/2013 : 05:37:05
|
Hi all
I need to execute a merge statement but the target table is on a remote server with the source table on my local server.
I've googled this but everything I've tried gives me an error of some description.
The initial merge statement said the destination can't be a remote table.
I then tried exec ..... at myservername but then it complains it can't see the source table. I'd rather not do a delete/insert if I can avoid it (just for neatness and speed if nothing else).
Anyone any ideas? |
|
|
James K
Flowing Fount of Yak Knowledge
1513 Posts |
Posted - 02/06/2013 : 07:51:10
|
| Is there any possibility that you can run the merge statement on the target server? If you are not able to do that, the only other possibility that I see is to do it the old way of delete/update/insert. |
 |
|
|
rmg1
Posting Yak Master
245 Posts |
Posted - 02/06/2013 : 07:56:45
|
The remote server can't see the local server (the link only works one way).
Looks like it'll have to be old-fashioned way. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1513 Posts |
Posted - 02/06/2013 : 08:14:46
|
| If the remote server cannot see the local server,I don't see any other reasonable way. Other ways that I can think of are all non-starters or will cause you to twist yourself into a knot! |
|
|
|
rmg1
Posting Yak Master
245 Posts |
Posted - 02/06/2013 : 08:52:05
|
Yeah, that's what I thought. I'll have to do it the hard way.
Just out of curiosity, what are the other methods you thought of? |
|
|
|
James K
Flowing Fount of Yak Knowledge
1513 Posts |
Posted - 02/06/2013 : 09:36:19
|
| I was thinking about running the query on the linked (remote server) - but that wouldn't work, because the remote server is unable to connect back to your local server. The other possibilities that I was thinking about where setting up some type of SSIS task or replication. All of those are overkills or way too complex when the option of using simple update/delete/insert is available to you. |
|
|
|
rmg1
Posting Yak Master
245 Posts |
Posted - 02/06/2013 : 09:46:52
|
Think I'll just stick with the delete/insert (don't need an update really).
It's already built and runs reasonably quickly. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1513 Posts |
Posted - 02/06/2013 : 10:13:59
|
| Wise move :) |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3831 Posts |
Posted - 02/06/2013 : 13:20:51
|
| You can certianly execute a Merge statement using a linked server, but only if the source is the linked server. Although linked servers have their place, I try to avoid them for production processes like this. As James suggested, SSIS is highly suited to this sort of task. The reason you want to avoid using linked servers is you can't control what the optimizer will do. Meaning, it may weill pull over all the data into tempdb before it does its work. I've seen this kill perormace in an ETL "system" writen by people that didn't/don't understand how the internals work. It took hours to process a single table using that method. Whereas, SSIS was able to do the same thing in minutes. YMMV depending on your particular scenario though. |
Edited by - Lamprey on 02/06/2013 13:30:04 |
|
|
| |
Topic |
|
Merge statement on a remote server
