I agree on the part that UPPER should be removed if the collation is case insensitive.

The other thing that would improve the query is if you avoid joining tables from a linked server with tables from the local database. Unless you can avoid that, the performance won't be optimal.

Can you confirm that the SELECT...INTO statement is the one with the problem?

If it's not, then post DDL for tables involved including indexes and actual execution plan. To know how to do it, check this article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/