Table Spool/Eager Spool

I have a query

select * into NewTab from OpenQuery(rmtServer, 'select c1, c2 from rmtTab')

When I look at the execution plan, it tells me that it performs a 'Table Spool/Eager Spool' that 'stores the data in a temporary table to optimize rewinds'

Now I don't anticipate any rewinds. If there is a crash of some sort, I can just drop newTab and start over.

Is there any way I can stop it from storing the data in a temporary table?

From stackoverflow

• create the NewTab first and then do insert into... from openquery.

• It's probably the openquery causing it.

  There is no information on how many rows, no statistics, nothing so SQL Server will simply spool the results to allow it to evaluate the later bits I suspect. That's the basic idea.

  I'd suggest separating the creation and fill of newtab.

  By the way, rewind is not rollback. Rewind has nothing to do with the transaction safety. It's SQL Server anticipating reuse of the rows. Which is correct, because the openquery is a black box.

  Look near the bottom of this Simple Talk article for rewinds. You have a "Remote query".

  Edit

  Based one something I found last week only, look at sp_tableoption.

  When used with the OPENROWSET bulk rowset provider to import data into a table without indexes, TABLOCK enables multiple clients to concurrently load data into the target table with optimized logging and locking

  Try TABLOCK on your fill. We had some fun with a client developer using .net SQLBulkCopy giving very bad performance.

  Also this from, Kalen Delaney

  It's not intuitive.

  cindi : I tried seperating the creation and fill (also suggested by Mladen), but I got the same query plan. But that link is very interesting.

  cindi : I suppose my simplistic view of the query is that a remote query is going to return a table of information, the select/into is going to create a blank table and then just copy whatever rows it gets into the new table. So why is it doing this twice?