x

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?

more ▼

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

Andrew Clarke gravatar image

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)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Oct 12 '09 at 03:39 PM

GSquared gravatar image

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)
10|1200 characters needed characters left

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.

more ▼

answered Oct 12 '09 at 02:11 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 12 '09 at 02:35 PM

Cade Roux gravatar image

Cade Roux
98 1

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 12 '09 at 02:32 PM

Jack Corbett gravatar image

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x24

asked: Oct 12 '09 at 01:35 PM

Seen: 2828 times

Last Updated: Oct 12 '09 at 02:12 PM