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

  • June 21, 2011 at 4:22 pm

    #1342165

    sqldba_icon (6/21/2011)

    GSquared (6/21/2011)

    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.

    Thanks for your reply..Hmm..i thought first creating a temp table then populating data would be preferred in my case. I guess i will test it out and see the performance

    It depends on the scenario. GSquared just conveyed a personal preference backed up by some performance findings and some nicetys offered by the technique.

    In the case of SELECT...*...INTO (in your post title), sometimes it's a good thing to leave columns added to the base schema after your code was originally written out of the temp tables. Sometimes you may want that behavior in which case SELECT * INTO would be a good thing.

    Whether supplying an explicit column list or a * SELECT...INTO generates a table with no clustered index, i.e. a HEAP. If you're managing large quantities of data in temp tables and you want a clustered index on the table you may be better off creating the table ahead of time and then doing the insert.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato