January 21, 2011 at 10:52 am
I recall an article once upon a time about the pros and cons of the two approaches to creating a new table with data from a query. One can either use 'create table as select x' or 'select x into table'. The end result is the same but I distinctly recall there were noticeable differences in the behind the scenes workings wanting consideration depending on your needs. I tried searching but can't find it. Does anyone remember the article or its conclusions?
January 21, 2011 at 10:57 am
SELECT * INTO NewTable FROM ... is the preferred method nowadays. That CREATE TABLE x AS SELECT ... sounds like an oracle command, since they like to make everything twice as complicated as it needs to be anyway.
It might have also been back in 6.5/7.0...I'm not sure when they introduced SELECT INTO.
January 21, 2011 at 10:57 am
I've seen a lot of discussions on the subject. I don't know the specific one you're looking for, but just searching "sql server "select into"" in Bing got a lot of data for me just now.
The conclusions on the subject vary a lot.
Select Into is often faster, but has the disadvantage that you can't fully constrain the table before creating it. Create then Insert...Select sometimes takes longer, but can be much faster on large or very complex data sets.
Select Into can also suffer from the IMEX issues that Excel and Jet sources have, depending on your source, and can thus end up with mistyped columns (NVarchar where they should be DateTime, and so on).
So, there are pros and cons to each.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply