Most efficient way to insert data to MS SQL table from OpenQuery on DB2 linked server? after much researching and experimenting, I figured I would make an attempt to get some expert advice.

I am maintaining a stored procedure that is executed as a job. It creates a table with data nicely defined. It then uses an OpenQuery to collect some data and insert it into the table. 

Example:

insert into LOCAL_TABLE 
select * 
from OPENQUERY(linked_server, 'select * from linktable')


The result of the openquery is 2.5 million rows. This takes roughly 2 hours to complete. 

The DB server is an iSeries so I ran the query with Visual Explain and the query completes in about 1 second. I am running the query from MS SQL Server 2016. Both servers sit right next to each other connected through a gigabit switch.

I am trying to identify the bottleneck here and I believe it is the INSERT. 

I have read about BULK INSERT and it would appear I cannot use BULK INSERT with an OpenQuery.

I have read about OPENROWSET(BULK... ) but I don't think I can use a linked server with this. I have to maintain using the linked server so that if the server changes the code does not need to be updated.

Finally, running this as SELECT * INTO may increase performance, but then the data types would be defined by the result set. I suppose I could work with it from there once it's local but I'd like to confirm this is worth the effort before embarking on it. 

Any recommendations as to how I can improve performance here?
I ran the query with Visual Explain and the query completes in about 1 second.


Then it probably didn't fetch 2.5 million rows, which is likely your bottleneck.


  I am trying to identify the bottleneck here and I believe it is the INSERT. 


Then try just running

select * 
from OPENQUERY(linked_server, 'select * from linktable')


To eliminate the INSERT.


  running this as SELECT * INTO may increase performance


Test that too.  And test INSERT INTO with a Temp Table.

Using a 4-part name should not improve performance.