none
T-SQL Difference Between Select INTO and Create Table Insert Into ?

    Question

  • Hi friends,

     

    I would like to know Main Technical Difference Between 

     

    Select INSERT INTO 

    and

    Create Table #Temp

    Insert Into #Temp

    Select * from Table

    Thanks

    Saturday, June 25, 2011 11:22 PM

All replies

  • You have limited options to define a table with SELECT INTO:

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

    For example, you cannot define nullability, default, PRIMARY KEY, or UNIQUE KEY.  You need additional statements to carry out a more complete definition.

    With CREATE TABLE, you can carry out an extensive definition:

    http://www.sqlusa.com/bestpractices2008/unique-constraint/

    SELECT INTO is very fast because it is minimally logged(exception FULL RECOVERY). SELECT INTO is also a quick way of creating a table from SELECT queries. SELECT INTO is a powerful tool for a DBA or database developer.

     

    Articles: INTO Clause (Transact-SQL)

    http://www.sqlusa.com/bestpractices2005/selectfromsproc/



    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam
    Saturday, June 25, 2011 11:31 PM
  • Dear Sir

     

    The SELECT INTO statement selects data from one table and inserts it into a different table.

    The SELECT INTO statement is most often used to create backup copies of tables.

    SELECT *
    INTO new_table_name [IN externaldatabase]
    FROM old_tablename

     

    Or we can select only the columns we want into the new table:

    SELECT column_name(s)

    INTO new_table_name [IN externaldatabase]
    FROM old_tablename

     

    If this work with you, please tell me

     

    Mona

    Sunday, June 26, 2011 3:22 AM
  • SELECT ... INTO ..

    is minimally logged operation, so it usually performs quicker than INSERT INTO counterpart.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, June 26, 2011 5:28 AM
  • > SELECT INTO is very fast because it is minimally logged.

    This needs some qualification.

    First of all, the operation is only minimally logged if the database is in simple or bulk_logged recovery mode. If the database is in full recovery, the operation is fully logged.

    Next, "very fast" depends on the condition in the SELECT query. If the SELECT is complex, it will not run any faster if you add INTO. What is fast is the data-load part of SELECT INTO.

    Furthermore, starting with SQL 2008, INSERT-SELECT can also be minimally logged. The full details are in Books Online, but in short, you need to use the TABLOCKX hint for the target table, and the table must be a heap. (There is a traceflag to enable minimally logged inserts into a clusterered index.) And of course the database must be in simple or bulk-logged recovery.

    Overall, I recommend against using SELECT INTO in production code. From a maintenance perspective, I think an explicit CREATE TABLE is better, since you know what you get. SELECT INTO can still be handy when you are investigating some situation in a database, and want to save an intermediate result in a temp table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, June 26, 2011 10:38 AM
  • Thanks for replyng me.

    My main question is that,

     

    Why SSELECt INTO Operation does minimal Logged 

    against Insert Into ?

     

    I would like to understand , why Select Into has minimal Lockinng ?

     

    Thanks.

    Sunday, June 26, 2011 1:10 PM
  • Thanks for replyng me.

    My main question is that,

     

    Why SSELECt INTO Operation does minimal Logged 

    against Insert Into ?

     

    I would like to understand , why Select Into has minimal Lockinng ?

     

    Thanks.

    Sunday, June 26, 2011 1:10 PM
  • Thanks for replyng me.

    My main question is that,

     

    Why SSELECt INTO Operation does minimal Logged 

    against Insert Into ?

     

    I would like to understand , why Select Into has minimal Lockinng ?

     

    Thanks.

    Sunday, June 26, 2011 1:10 PM
  • Thanks for replyng me.

    My main question is that,

     

    Why SSELECt INTO Operation does minimal Logged 

    against Insert Into ?

     

    I would like to understand , why Select Into has minimal Lockinng ?

     

    Thanks.

    Sunday, June 26, 2011 1:10 PM
  • See the following demo:

    USE tempdb;
    GO
    DBCC DROPCLEANBUFFERS
    GO
    DECLARE @writes BIGINT = @@TOTAL_WRITE
    SELECT * INTO SOD FROM AdventureWorks2008.Sales.SalesOrderDetail
    SELECT Writes = @@TOTAL_WRITE - @writes  -- 150
    GO
    DROP TABLE tempdb.dbo.SOD
    GO
    
    CREATE TABLE [dbo].[SOD](
    	[SalesOrderID] [int] NOT NULL,
    	[SalesOrderDetailID] [int] NOT NULL,
    	[CarrierTrackingNumber] [nvarchar](25) NULL,
    	[OrderQty] [smallint] NOT NULL,
    	[ProductID] [int] NOT NULL,
    	[SpecialOfferID] [int] NOT NULL,
    	[UnitPrice] [money] NOT NULL,
    	[UnitPriceDiscount] [money] NOT NULL,
    	[LineTotal] [numeric](38, 6) NOT NULL,
    	[rowguid] [uniqueidentifier] NOT NULL,
    	[ModifiedDate] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO
    DBCC DROPCLEANBUFFERS
    GO
    DECLARE @writes BIGINT = @@TOTAL_WRITE
    INSERT SOD
    SELECT * FROM AdventureWorks2008.Sales.SalesOrderDetail
    SELECT Writes = @@TOTAL_WRITE - @writes  -- 210
    GO
    GO
    DROP TABLE tempdb.dbo.SOD
    GO
    
    


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam
    Sunday, June 26, 2011 2:13 PM
  • Why SSELECt INTO Operation does minimal Logged 

    against Insert Into ?


    Historical reasons. In early version of SQL Server, you could only use SELECT INTO if the database had a certain option set. In practice, you only used SELECT INTO to create temp tables in those days.

    But there is also this thing about exclusive locks on the table. Nothing can be minimally logged if there are concurrent inserts.

    As I said in my previous post, in SQL 2008, INSERT INTO can also be minimally logged.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, June 26, 2011 7:14 PM
  • Overall, I recommend against using SELECT INTO in production code.

    Erland,

    Can you clarify that?  I believe that SELECT INTO is perfectly applicable for database development, administration and production. Remember production includes night jobs as well when you need to shuffle data around.

    Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam
    Sunday, June 26, 2011 8:38 PM
  • I simply think that CREATE TABLE makes code more clearer as it states the intent more clearly. SELECT INTO has a sloppy feel over it. SELECT INTO also breaks down if you want to insert in two different ways:

    IF @this = 1
       SELECT .... INTO #temp FROM ....
    ELSE
       SELECT .... INTO #temp FROM ....

    This gives you a compile-time error that #temp already exists. (Which is a crazy leftover from SQL 6.5, but nevertheless.)

    Previously, an argument for SELECT INTO was that it was minimally logged, but you can now achieve the same thing with INSERT.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, June 26, 2011 9:14 PM
  • Speed reason for SELECT INTO is one thing. Can you give a sample script for the minimally logged version of INSERT SELECT?  Thanks.

    A second benefit of SELECT INTO is a simple single statement table create and populate.

    Finally, the real magic of SELECT INTO is creating meta data when you don't know the output structure:

     

    USE AdventureWorks2008;
    GO
    SELECT * INTO SPWHO FROM OPENQUERY(HPESTAR, 'exec sp_who')
    -- (39 row(s) affected)
    EXEC sp_help SPWHO;
    GO
    /* 
    ....
    spid	smallint	no	2
    ecid	smallint	no	2
    status	nchar	no	60
    loginame	nvarchar	no	256
    hostname	nchar	no	256
    blk	char	no	5
    dbname	nvarchar	no	256
    cmd	nchar	no	32
    request_id	int	no	4
    .....
    */
    
    

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam
    Sunday, June 26, 2011 9:25 PM
  • Speed reason for SELECT INTO is one thing. Can you give a sample script for the minimally logged version of INSERT SELECT?  Thanks.

    You find the details under the topic for INSERT in Books Online.

    A second benefit of SELECT INTO is a simple single statement table create and populate.

    Which is good for quick testing or storing an intermediate result when playing around. But that is no virtue of importance in code that is to be maintained.

    Finally, the real magic of SELECT INTO is creating meta data when you don't know the output structure:


    I would never agree to permit the usage of OPENQUERY for that reason in production code. There is a considerable overhead, and it can easily break. See http://www.sommarskog.se/share_data.html#OPENQUERY
    for more details.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, June 27, 2011 8:50 PM
  • Erland,

    Your concern about production code to be solid is well-taken. I can't agree more.

    However, there is "life" outside production in database development and administration.  Not sure if these are the areas you refer to as "playing around".

    I never used INSERT SELECT with minimal logging. Therefore I (and forum readers) would really appreciate if you post an example script. Thanks.

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam
    Tuesday, June 28, 2011 7:37 AM
  • Best Practices
    http://technet.microsoft.com/en-us/library/ms174335.aspx

    Using INSERT INTO…SELECT to Bulk Import Data with Minimal Logging

    The recovery model of the database is set to simple or bulk-logged.

    The target table is an empty or nonempty heap.

    The target table is not used in replication.

    The TABLOCK hint is specified for the target table.

    INSERT INTO <target_table> WITH (TABLOCK)
    SELECT <columns> FROM <source_table>


    Jon
    Tuesday, June 28, 2011 7:54 AM
  • However, there is "life" outside production in database development and administration.  Not sure if these are the areas you refer to as "playing around".


    And my comment was specifically for code that runs in production. I use SELECT INTO myself frequently for throw-away things.

    I never used INSERT SELECT with minimal logging. Therefore I (and forum readers) would really appreciate if you post an example script. Thanks.

    All that is special is the TABLOCKX hint. I wonder if it's needed for a local temp table. It should not, I think, but I have heard nor investigated what applies in this case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 28, 2011 8:03 AM
  • Thanks Jon. Following script is the implementation of INSERT SELECT with minimal logging. TABLOCK and TABLOCKX gave similar results.

     

    USE tempdb;
    GO
    DBCC DROPCLEANBUFFERS
    GO
    DECLARE @writes BIGINT = @@TOTAL_WRITE
    SELECT * INTO SOD FROM AdventureWorks2008.Sales.SalesOrderDetail
    SELECT Writes = @@TOTAL_WRITE - @writes -- 150
    GO
    DROP TABLE tempdb.dbo.SOD
    GO
    
    CREATE TABLE [dbo].[SOD](
    	[SalesOrderID] [int] NOT NULL,
    	[SalesOrderDetailID] [int] NOT NULL,
    	[CarrierTrackingNumber] [nvarchar](25) NULL,
    	[OrderQty] [smallint] NOT NULL,
    	[ProductID] [int] NOT NULL,
    	[SpecialOfferID] [int] NOT NULL,
    	[UnitPrice] [money] NOT NULL,
    	[UnitPriceDiscount] [money] NOT NULL,
    	[LineTotal] [numeric](38, 6) NOT NULL,
    	[rowguid] [uniqueidentifier] NOT NULL,
    	[ModifiedDate] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO
    DBCC DROPCLEANBUFFERS
    GO
    DECLARE @writes BIGINT = @@TOTAL_WRITE
    INSERT SOD
    SELECT * FROM AdventureWorks2008.Sales.SalesOrderDetail
    SELECT Writes = @@TOTAL_WRITE - @writes -- 210
    GO
    TRUNCATE TABLE SOD
    GO
    DBCC DROPCLEANBUFFERS
    GO
    DECLARE @writes BIGINT = @@TOTAL_WRITE
    INSERT SOD WITH (TABLOCK)
    SELECT * FROM AdventureWorks2008.Sales.SalesOrderDetail
    SELECT Writes = @@TOTAL_WRITE - @writes -- 50
    GO
    DROP TABLE tempdb.dbo.SOD
    GO
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam
    Tuesday, June 28, 2011 8:10 AM