6

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.

ChaseMedallion
  • 20,860
  • 17
  • 88
  • 152
  • 1
    Looks pretty comprehensively answered in your link. If you don't want the retry code what about the other option of taking a shared table lock on the source table? – Martin Smith Sep 11 '13 at 21:35
  • Is there any reason (other than saving a minute or two writing out the statments) that you can't explicitly create your table with a `CREATE TABLE` statement, then use `INSERT INTO Table (columns) SELECT ...`? I think this would solve your problem, `SELECT .. INTO ..` is a great time saver for ad hoc scripts, but I can't think of any situation where I would use it in production code. – GarethD Sep 11 '13 at 21:39
  • While I can think of situations where I'd use it in production code, I primarily agree with Gareth, and if the columns are unknowable at development time, you could always build & execute it as dynamic sql. – John Bingham Sep 11 '13 at 21:53

1 Answers1

2

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;
PowerUser
  • 11,583
  • 20
  • 64
  • 98