SELECT INTO

Kevin Feasel

2016-10-21

T-SQL

Daniel Janik is not a fan of SELECT INTO:

This query for AdventureWorks will dump all of its results into a table named #MyDuplicateCities. Note that there is no CREATE TABLE statement. The INTO [tablename] will create the table for you.

Running this query a second time will result in failure if you haven’t dropped the #MyDuplicateCities table.

Using this syntax can be really helpful if you just need to do some quick and dirty cleanup; however, it should be avoided for stored procedures. Here’s why…

There are some trade-offs here and good arguments either way.  The comments tend to take the pro approach, so they’re worth reading as well.

Related Posts

Updating Tables With Faked Data

Phil Factor continues his data obfuscation series: We are taking a slow-but-steady approach. We rewrite our code from the previous blog post that assembles the string; it now uses a view to get its random numbers, and we’ll speed it up slightly by putting a bit more intelligence into the markov table. We then put […]

Read More

COUNT And NULL Values

Kenneth Fisher digs into the COUNT() function and sees how it deals with NULL values: Count the number of values 1 2 3 SELECT COUNT(FieldName) FROM TableName; -- or SELECT COUNT(ALL FieldName) FROM TableName; The ALL argument is the default and is unnecessary (I didn’t even know it existed until I started this post). Here you are counting […]

Read More

Categories

October 2016
MTWTFSS
« Sep Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31