Update table in Database through linked server

Tags:
SQL
SQL Query
SSMS
I want to update my TableA on my ServerA by using linked server which is on serverB.I have successfully created the linked server but i am a little bit confused. Actually i have to update a columnA of TableA from the data coming from columnB(TableB) of linked server on the basis of the matched phoneNo in both of the tables. The confusion is that the phoneNo format coming from the linked server is in format "911234567891" and the phoneNo that are in my TableA is of format 123-456-7891. So i have to remove the 91 digit first then i have to convert this number into xxx-xxx-xxxx this format inorder to compare it with the column of TableA. I have written the following queries for changing the phone format and removing the 91 from start.
 SELECT
     [Phone Numbers]
    ,FORMAT([Phone Numbers],'###-###-####') AS [Formatted Phone]
    FROM tbl_sample

UPDATE tbl_sample
SET    [Phone Numbers] = SUBSTRING([Phone Numbers], 3, 8000) 
WHERE [Phone Numbers] LIKE '91%'
But I have no idea how I can can combine both of them in same query and then use it to update the data in TableA of serverA but the most frustrating thing is that I have to schedule this update after every 24 hours. Any kind of help/suggestion will be appreciated.
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    What type of data base is the linked server connecting to, a DB2, SQL, Oracle or other? Can you post the rest of the code, and which database do you need to update, the local or the linked?
    128,395 pointsBadges:
    report
  • hussainahmad
    Its SQL
    i want to update my localTable
    UPDATE LocalTable 
    SET new_LastPhoneCall = i.CallTime 
        
    FROM Openquery(linkedserverName, 'SELECT CallTime,
    		    Substring(T.TerID,3,3) + ''-'' 
               + Substring(T.TerID,6,3) + ''-'' 
               + Substring(T.TerID,9,4) AS ''Phone Number-Formatted''
               
    FROM LinkedSeverDBTable as T where TerID LIKE ''91%''') i
    WHERE  i.[Phone Number-Formatted] = LocalTable.Telephone1
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: