Difference Between Select into and Create and insert Statement in SQL

SELECT INTO STATEMENT

ü  SELECT INTO is very fast because it is minimally logged(Full details given below).

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

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

ü  Another benefit of SELECT INTO is a simple single statement table create and populate.

ü  SELECT INTO is creating meta data when you don't know the output structure.

ü  SELECT INTO creates the table specified after INTO. If the table exists, you get an error. Obviously Insert Into requires that the table already exist.

CREATE TABLE AND INSERT INTO STATEMENT

ü  This is the step by step way to create a table and insert data into table.

ü  You have more control over the table's schema. Ie. you can create indexes and other keys on a table when you Create it, you can't do that with Select Into.

Note: Create Table, the locks are acquired and released immediately once the table is created. but, Select Into, the locks are acquired and held until the transaction completes. So something like this :

select  ( Huge amount of records ) into otherTable

This query will hold locks on system tables until all rows have been inserted. So it is very expensive.

SELECT...INTO creates a new table, while INSERT...SELECT requires an existing table. So there are security implications and possible performance issues to consider.

Also, with SELECT...INTO, the new table has the same columns but no keys or constraints, so it is best for simply creating a temp backup.

When you have a very long-running query in a temporary table using the SELECT INTO format, those same system table locks are held until the query completes and data loads into the temp table. You can avoid system table locking by manually creating the table with the CREATE TABLE command – before loading the data into the table.

For example, this code …

CREATE TABLE #TempTable
(spid int)

INSERT INTO #TempTable
SELECT spid
FROM sys.objects

… will require much less locking than this code:

SELECT spid
INTO #TempTable
FROM sys.objects

While the total number of locks taken is the same, the length of time the locks are held for the first query will be much shorter. This allows other processes to create temp tables.

Typically, when developing SQL code the development server has only a single user or few users. When working on SQL code, it's important to know when the code will impact sessions other than the current session. And unexpected interaction can cause major performance issues.