Is there any way to safely run SELECT INTO?

Question

I have a script that runs a SELECT INTO into a table. To my knowledge, there are no other procedures that might be concurrently referencing/modifying this table. Once in awhile, however, I get the following error:

Schema changed after the target table was created. Rerun the Select Into query.

What can cause this error and how do I avoid it?

I did some googling, and this link suggests that SELECT INTO cannot be used safely without some crazy try-catch-retry logic. Is this really the case?

I'm using SQLServer 2012.

Answer 1

Unless you really don't know the fields and data types in advance, I'd recommend first creating the table, then adding the data with an Insert statement. In your link, David Moutray suggests the same thing, here's his example code verbatim:

CREATE TABLE #TempTableY (ParticipantID  INT  NOT NULL);

INSERT #TempTableY (ParticipantID)
SELECT ParticipantID
FROM   TableX;