| Author |
Topic |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2013-02-06 : 05:37:05
|
| Hi allI 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-06 : 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
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-02-06 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-06 : 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
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-02-06 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-06 : 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
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-02-06 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-06 : 10:13:59
|
| Wise move :) |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-06 : 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. |
|
|
|
|
Merge statement on a remote server