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, 2009 at 01:35 PM in Default

avatar image

Andrew Clarke
56 2 2 4

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

Oct 12, 2009 at 02:12 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

5 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, 2009 at 03:39 PM

avatar image

GSquared
88 2 1

(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, 2009 at 02:11 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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, 2009 at 02:35 PM

avatar image

Cade Roux
98 2 2

(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, 2009 at 02:32 PM

avatar image

Jack Corbett
1.1k 3 4 7

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

Bear in mind also that if your database is set to a simple or bulk-logged recovery model, that SELECT... INTO is minimally logged which can provide a substantial performance gain.

more ▼

answered Sep 09, 2014 at 12:03 PM

avatar image

GPO
4.8k 40 49 56

(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.

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:

x1069
x31

asked: Oct 12, 2009 at 01:35 PM

Seen: 4712 times

Last Updated: Sep 09, 2014 at 12:03 PM

Copyright 2016 Redgate Software. Privacy Policy