Is it now OK to use the SELECT...INTO clause?

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?

asked Oct 12 '09 at 01:35 PM in Default

Andrew Clarke
36 ● 2 ● 2 ● 2

I just added the 'blocking' tag here, hope you don't mind!

Oct 12 '09 at 02:12 PM Matt Whitfield ♦♦

(comments are locked)

4 answers:

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.

answered Oct 12 '09 at 03:39 PM

GSquared
78

I have seen this issue with contention in tempdb in SQL server 2000, but I think it was fixed with sp3 or sp4. I still use create and insert just in case.

Oct 20 '09 at 04:44 PM Håkan Winther

As a matter of fact, I am having this issue right now in SQL Server 2008! Someone is using a stored procedure with select * into #tmp from a_slow_running_query.

Oct 22 '09 at 08:18 AM Håkan Winther

(comments are locked)

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.

answered Oct 12 '09 at 02:11 PM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

(comments are locked)

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.

answered Oct 12 '09 at 02:32 PM

Jack Corbett
1.1k ● 2 ● 2 ● 3

I can't find a good link on MSDN - I think they've retired a lot of the content from that sort of time (it being 10+ years ago :) ) - but there are plenty of sources on the net discussing it being fixed in 7 - for example http://stackoverflow.com/questions/1302670/sql-server-select-into-and-blocking-with-temp-tables

Oct 12 '09 at 03:46 PM Matt Whitfield ♦♦

(comments are locked)

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.

answered Oct 12 '09 at 02:35 PM