April 11, 2012 at 7:12 am
Hello all,
I've 2 separate sql servers, both 2008R2. On srv1 i created a link to linksrv in order to update data between 2 tables. essentially what i want is to update (localserver) srv1.db.tbl.columnX. with data from linksrv.db.tbl.columnY where srv1.db.tbl.columnA = linksrv.db.tbl.columnB . Any ideas? It seems so simple but I am so new to this I don't really know where to start... Any help is appreciated.
April 11, 2012 at 7:22 am
If you have the linked servers setup correctly it's as simple as referencing a table via
[linked server name].[database name].[schema name].
Where the linked server name is the name you called the linked server.
That said, unless you have only a small number of records this is going to be pretty slow as linked servers handle these sorts of things with internal cursors.
I've found in the past that from the destination server pulling the source server into a local temp table while costly can improve performance, as could an SSIS job.
I cringe when I need to update or join between servers.
April 12, 2012 at 7:58 am
danderson 73949 (4/11/2012)
Hello all,I've 2 separate sql servers, both 2008R2. On srv1 i created a link to linksrv in order to update data between 2 tables. essentially what i want is to update (localserver) srv1.db.tbl.columnX. with data from linksrv.db.tbl.columnY where srv1.db.tbl.columnA = linksrv.db.tbl.columnB . Any ideas? It seems so simple but I am so new to this I don't really know where to start... Any help is appreciated.
i think you are missing your schema you have posted for all of your tables [Server].[Database].[Table].[Column] it needs to be as mtassin said [Server].[Database].[Schema].[Table].[Column] an example would be server1.dbname.dbo.tab1.col1
maby something like this:
UPDATE server1.db.dbo.tab1 set col1 = server2.db.dbo.tab2.colY
WHERE server1.db.dbo.tab1.ID = server2.db.dbo.tab2.ID
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply