Best way to speed up a linked server query - 49 columns plus two calculated columns and 170 mill rows I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them. 
Trying to insert this query in a table on destination server takes over 2 hours. And then the index takes between 30 min and one hour. It is to be used in a query, joining col51 with another table, and col50 is used in the where clause.

Is there any way we can speed this up or use a different approach? 

Here is the query:

SELECT * into destination.table FROM OPENQUERY(linkedserver,'
SELECT 
col1
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col30
col31
col32
col33
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
   ,BINARY_CHECKSUM(
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
) AS col50
   ,CASE WHEN col1= 1  THEN CONVERT(BIGINT,CONCAT(1*100,col8))
         WHEN col1= 2  THEN CONVERT(BIGINT,CONCAT(2*100,col13))
         WHEN col1= 3  THEN CONVERT(BIGINT,CONCAT(3*100,col9))
         WHEN col1= 4  THEN CONVERT(BIGINT,CONCAT(4*100,col10))
         WHEN col1= 10 THEN CONVERT(BIGINT,CONCAT(10*100,col11))
         ELSE CONVERT(BIGINT,CONCAT(col1*100,col3))
        END AS col51 
  from source_server.table')


After inserting to table, this query is the other part - which also takes a long time. Suggestions to enhance it?

SELECT 
           ISNULL(A.Col1,B.Col1) Col1
          ,ISNULL(A.Col2,B.Col2) Col2
          ,ISNULL(A.Col3,B.Col3) Col3
       ...etc
         ,ISNULL(A.Col50,B.Col50) Col50 
         ,ISNULL(A.Col51,B.Col51) Col51 
  FROM Table1 A -- linked server table
  FULL OUTER JOIN Table2 B ON A.Col51=B.Col51 --local table
  WHERE (B.Col50 IS NULL AND A.Col50 IS NOT NULL)--new rows      
       OR  (A.Col50 IS NULL AND B.Col50 IS NOT NULL)--deletes 
       OR  (B.Col50 IS NOT NULL AND A.Col50 IS NOT NULL AND B.Col50 &lt;&gt; A.Col50) --updates
it is in some way unreadable. Why not trying to call a stored procedure and getting the dataset?

select * from 
OPENQUERY(link_server_name,'exec your_sp par1,par2)


What's more, instead of using CONVERT what about of using CAST?

CAST is ANSI compliant.

Best,