Nightly reload of tables from a linked server I have a number of tables that need to be reloaded nightly from a linked server.  My current method of doing this is to create a nightly job to pull the data using something like the following:

TRUNCATE TABLE [TableA]
INSERT INTO dbo.[TableA]
    SELECT [Col1], [Col2], [Col3]
    FROM [linked].[server].[dbo].[TableA]


On some of these tables have millions of rows of data are taking hours to complete.  The job sometimes finishes after 8am which is causing performance issues for the applications using the database due to users logging in and querying the db.  Most of the tables have multiple indexes.  

I'm curious if there is a better method to speed up the process. Furthermore, is it possible and would it be faster to not truncate the table and instead only pull over any new or changed data for each of the tables.
It depends, but options include (and are not limited to):


Restore a backup of the linked database on the local instance (for better uptime, restore to a new name, then DROP DATABASE the old one and then sp_rename the fresh restore), and either

create synonyms to the restored copy from your current local database so as to allow current queries to continue

optionally drop every table except the ones you need, SHRINK, and then do index maintenance to make up for the index fragmentation the SHRINK caused (all before the sp_rename)

do your "table refresh" from the restored database, then DROP DATABASE the restore database

Use OPENQUERY or EXECUTE AT to send a good JOIN and/or WHERE clause or set of clauses to the linked server, including the maximum update date your local server has, so you retrieve (and insert or update) only the rows that have been inserted or updated since your last refresh

deal with deleted rows painfully

Use a history table on the remote database to track inserts, updates, and deletes, and use that in your JOIN and/or WHERE clauses sent using OPENQUERY or EXECUTE AT.