schema changed after the target table was created. rerun the select into query

Question

schema changed after the target table was created. rerun the select into query

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

OTF SSCertifiable Points: 6104 More actions · Answer 1

SQL Kiwi (6/8/2012)
MichaelMontgomery (6/8/2012)
I have run the script and no triggers exist on the database or server...Hopefully this makes it a little clearer.
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.

A rather interesting nugget here.

Thanks for sharing, Paul.

Paul White SSC Guru Points: 150467 More actions · Answer 2

David Moutray (6/24/2012)
Would this avoid error 539 ("schema changed after the target table was created")?

Yes. Error 539 is specific to SELECT...INTO, and the way it is implemented internally. With CREATE then INSERT, the INSERT will merely recompile as usual if a schema change occurs.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi