Is there a way to safely run SELECT INTO?

Question

I have a script that runs SELECT INTO in a table. As far as I know, there are no other procedures that could reference / modify this table at the same time. However, after a while I get the following error:

The scene changed after the destination table was created. Repeat selection to request.

What can cause this error and how to avoid it?

I did some search queries, and this link suggests that SELECT INTO cannot be used safely without any crazy try-catch-retry logic. Is this really so?

I am using SQLServer 2012.

Answer 1

If you really do not know the fields and data types in advance, I would recommend creating the table first and then adding the data using the Insert statement. In your link, David Mutray offers the same thing, here is his sample code verbatim:

CREATE TABLE #TempTableY (ParticipantID INT NOT NULL); INSERT #TempTableY (ParticipantID) SELECT ParticipantID FROM TableX;