I was brought up to believe that one should only use SELECT..INTO in development work but never in production code. In fact I've often experienced application lock-ups due to a programmer doing a SELECT.. INTO and locking tempDB. I gather that it is now OK to use it whenever one feels like it. When did it become OK, and when would one ever use this in preference to an explicit table creation?
I use Select Into pretty regularly. The tempdb locks were fixed a long time ago (SQL 7 or 2000, I don't remember which).
I've done speed tests, and Select Into is generally measurably faster than separate create and insert statements.
It comes in extremely useful when using Select Into From OpenRowset for importing from text files, csv files, etc., because you often don't have a detailed definition for the data model of the text file, and can easily end up with truncation errors if you create the table explicitly. This allows creation of a temp import table, and then, using tempdb.sys.columns and sys.columns (for the target database), you can compare data types and lengths and do error handling that makes more sense.
That issue was fixed in SQL Server 7, I believe.
I would personally always prefer to see an explicit CREATE beforehand, because it is a lot more accessible to someone who later reads the script.
I agree with Matt, that I like the Create. I'm not sure that this issue is fixed though. I'd like to see a link before I vote for Matt's answer.
It's no longer an issue (try these links), but typically, I still use the workaround where you SELECT INTO whatever WHERE 0 = 1 (and then a separate INSERT later) - this creates the table with all the original data types and NULLable options.
There is a benefit of doing it this way instead of CREATE from what you think the column names and types are. If you use SELECT INTO, you will see what SQL Server is using as the result of your expressions and you might find casting which is unintentional or incorrect - particularly in numeric lengths or string lengths - or what kinds of nullability are inferred.
Later I can fix the table definition and turn it into a proper CREATE.
1 Person is following this question.
