That helps a lot, thanks. The cause of your occasional problems is that SELECT...INTO is a bit of an unusual statement: it executes in two parts. You can't see this directly in the query plan, but the engine first inspects the query to determine the schema of the table that will be created. It then creates the table, compiles a query plan to insert rows to the new table, and then executes the query. By the way, the two main steps (create, then insert) are part of the reason that SELECT...INTO creates the table even if an error occurs:

-- Divide by zero...

SELECT *

INTO #Banana

FROM sys.columns AS c

WHERE 1/0 = 50

-- ...but the table is still created

SELECT * FROM #Banana;

DROP TABLE #Banana;

There is a small window between creating the table and starting to perform the insert, where the source tables in the query are not protected by schema stability locks. If a concurrent process happens to change the schema of one of the source tables, after the target has been created, but before the insert starts, error 539 ("Schema changed after the target table was created. Rerun the Select Into query.") is raised.

In your case, the concurrent disabling and rebuilding of non-clustered indexes on the source tables occasionally hits this window and causes the error (disabling or rebuilding an index is a schema change). So, you need to plan for the possibility of a 539 error when using SELECT...INTO with source tables that are visible to other processes. You can use a TRY...CATCH block with a retry if there's no open transaction you need to protect (error 539 dooms the current transaction). If that works for you, something like this would work:

Retry:

BEGIN TRY

-- For retry

IF OBJECT_ID(N'tempdb..#Temp', N'U')

IS NOT NULL

DROP TABLE #Temp;

SELECT

s2.col2,

s2.col1

INTO #Temp

FROM dbo.Source1 AS s1

JOIN dbo.Source2 AS s2 ON

s2.col1 = s1.col1;

END TRY

BEGIN CATCH

IF ERROR_NUMBER() = 539 GOTO Retry

-- Handle other errors

END CATCH;

Otherwise, we need to hold some sort of lock on the source table(s) across both parts of the SELECT...INTO statement to ensure the schema stays stable. Just adding a locking or isolation hint on its own will not help, because the target table creation happens within a system transaction, not the implicit user transaction provided by the statement itself. An explicit transaction is definitely required as well. One option is to use an explicit transaction with a held shared lock on the tables you need to protect:

BEGIN TRANSACTION;

SELECT

s2.col2,

s2.col1

INTO #Temp

FROM dbo.Source1 AS s1 WITH (REPEATABLEREAD, TABLOCK)

JOIN dbo.Source2 AS s2 WITH (REPEATABLEREAD, TABLOCK) ON

s2.col1 = s1.col1;

COMMIT TRANSACTION;

There are many other locking/isolation level options (and other query tricks) with their own advantages and disadvantages. Let us know if you have specific concurrency needs for the source tables while the SELECT...INTO is running, beyond what I have covered above.