Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Updating data in a remote table by using a linked server

I am trying to update a column in one database from a select statement on another database.
lets call database1 = weis1 and is on server1
and database2 = weis2 and is on server2

EXEC sp_addlinkedsrvlogin 'Accounts', 'true'

update server1.weis1 .[dbo].[CAT_RootOrg]
      set CAT_RootOrg.SKUCode=999999
      From server1.weis1.[dbo].[CAT_RootOrg] t1
            join server2.weis2 .[dbo].[ITEM_MASTER] t2
                  on t1.upc = t2.upc
         where server2.weis2 .[dbo].[ITEM_MASTER].upc=null

Sorry i am just a novice at best when comes to sql. Any help on this would be great.
  • 2
1 Solution
lcohanDatabase AnalystCommented:
Are you getting any error or what is the issue? The only problem I see assuming the user running it has all necessary rights in place is that it will be very slow on large volumes. Can you create a view on Server2 and filter that data at least right at the source?
lcohanDatabase AnalystCommented:
--assuming this "where server2.weis2 .[dbo].[ITEM_MASTER].upc=null"
--will reduce a lot the server2 recordset
use weis2
create view v_items2join as
select upc from [dbo].[ITEM_MASTER]
where [dbo].[ITEM_MASTER].upc=null

--on Server1:
update server1.weis1.[dbo].[CAT_RootOrg]
      set CAT_RootOrg.SKUCode=999999
from server1.weis1.[dbo].[CAT_RootOrg] t1
      inner join server2.weis2.[dbo].v_items2join t2 on t1.upc = t2.upc
jacobJLAuthor Commented:
Thanks icohan. The problem is more of permissions thing. My DBA will have to run it. but your query above helps a lot. Thanks so much

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now