Home Forums SQL Server 2005 Administering Which one is better select * into # temp from tableA Vs create #temp table insert into... RE: Which one is better select * into # temp from tableA Vs create #temp table insert into...

  • I prefer Select Into, because in performance tests I've done it's slightly faster, and because it will preserve DDL changes in the source Select between iterations.

    E.g.: If you have to expand one of the columns from varchar(25) to varchar(50) in one of the tables you are selecting from, Select Into will automatically create the temp table with the expanded column, but Create...Insert...Select will require changing the Create statement. That's usually a benefit, but it can be a problem if you're expecting an error in that case.

    - 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