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.