SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Merge statement on a remote server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 02/06/2013 :  05:37:05  Show Profile  Reply with Quote
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

3744 Posts

Posted - 02/06/2013 :  07:51:10  Show Profile  Reply with Quote
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.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 02/06/2013 :  07:56:45  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/06/2013 :  08:14:46  Show Profile  Reply with Quote
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!
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 02/06/2013 :  08:52:05  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/06/2013 :  09:36:19  Show Profile  Reply with Quote
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.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 02/06/2013 :  09:46:52  Show Profile  Reply with Quote
Think I'll just stick with the delete/insert (don't need an update really).
It's already built and runs reasonably quickly.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/06/2013 :  10:13:59  Show Profile  Reply with Quote
Wise move :)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/06/2013 :  13:20:51  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000