0

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.

Hussain Ahmad
  • 13
  • 1
  • 5
  • Does your linked server have an alias? you can use the alias in your update query to update table on ServerB – Bat_Programmer Jun 10 '16 at 02:09
  • Assuming you cleaned the data already, is this what you're looking for? UPDATE TableA SET ColumnA1 = source.ColumnB1 FROM TableA JOIN ( SELECT ColumnB1, ColumnB2,... FROM [LINKED_SERVER_NAME/ALIAS].[DATABASE_NAME].[dbo].[TABLE_NAME] --where clause?? ) source on TableA.ColumnA2 = source.ColumnB2 --where clause? – Dennis Jun 10 '16 at 02:13
  • thanks for the reply but i have to change the phone format to xxx-xxx-xxxx format in order it to compare it with my TableA. should i create a tempTable in my ServerA and stored all the data in it and then change phone format and then compare it with my TableA? but is it possible to create a job of it and run it on regular basis? – Hussain Ahmad Jun 10 '16 at 02:20
  • dump server b data in temp table at server and simply do join and update. – sandeep rawat Jun 10 '16 at 03:04

0 Answers0