none
Insert into Select Vs Select into

    Question

  • AS per performance wise which one is the better to use " Insert into select" or " Select Into"? I am using SQL Server 2005 EE 64 bits. The environment has high volumn of trafic.

    Thanks

    Saumen

    Wednesday, July 01, 2009 11:35 PM

Answers

All replies

  • Select ...Into creates a new table. This is a great way to create a table and populate with data quickly. Once the table is created, you then can use:

    Insert Into..
    Select ...
       From

    At the end of the day, both insert data into tables. The difference is that Select into creates the table.

    Hope this helps..

    JP
    • Proposed as answer by JohnVPetersenMVP Wednesday, July 01, 2009 11:45 PM
    • Marked as answer by Zongqing Li Thursday, August 20, 2009 7:59 AM
    Wednesday, July 01, 2009 11:45 PM
  • if your environment has high volume of trafic, I would suggest using insert into ....select. the table receiving the data should be created permanently, I meant, don't create it within your procedure. Frequently creating/dropping table will create more overhead, for instance, table schema creation, PFS contention, query recompile, potentially poor statistics, index issues. I would vote create table first. use insert into select in you procedure.
    John Huang
    Thursday, July 02, 2009 12:02 AM
  • It all depends.... :)

    Where is your datafiles created? How many datafiles, how many raids? How is your tempdb placed and where? 

    How often do you need this temp-table? Can you reuse it? Does it have any indexes? Do you need the procedures to run simultaneously or are you satisfied with a more procedural approach?




    Saturday, July 04, 2009 10:54 PM
  • In case of SELECT INTO. the table and column level constraints will not be taken case of like NOT NULL etc.,

    In case of INSERT INTO and SELECT, we should ensure that the table is pre-existing and all the constraint creation will be taken care of.

    Now the key point here is when can we create the constraints. Creation of indexes anyways is recommended to be done post data load in case if the data that is getting loaded into the table is huge.

    So, I feel, we need to take a call based on these points.

    Regards,
    Phani
    Sunday, July 05, 2009 5:39 AM
  • AS per performance wise which one is the better to use " Insert into select" or " Select Into"? I am using SQL Server 2005 EE 64 bits. The environment has high volumn of trafic.

    SELECT INTO is usually much better performance wise since it is minimally logged.

    INSERT INTO SELECT is logged, therefore it is generally slower (there is a minimally logged option but not trivial).

    Article: http://www.sqlusa.com/bestpractices/select-into/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Sunday, July 05, 2009 9:15 AM