SELECT INTO (vs) INSERT INTO SELECT in MS-SQL Server

Before getting into the concept first let us understand the number of ways to insert data into the MS SQL Server table

1. Using any ETL Tools: Such as SSIS, Informatica Ab Initio, Azure Data Factory, AWS Glue Etc

2. Using Import data task available in MS SQL Server database tasks

3. Using SQL Statements

 

So, using the first and second methods require respective 

ETL knowledge.

 

in other hand, as we are SQL Developers we prefer to load it using the SQL Queries/Statements.

 

INSERT INTO SELECT and SELECT INTO are nearly similar commands used to load data to tables with some significant differences in their use case. The SQL Developers who are at beginner level, even experts, gets confused about which one to use and what will happen if I use another command in existing place. 

So, Let's get this cleared.

Syntax: (in MS SQL Server)

INSERT INTO SELECT:

    CREATE TABLE MyDataBase.dbo.DataBaseObjects
    (
	[Name] NVARCHAR(250),
	[Type] NVARCHAR(150)
    )
    INSERT INTO MyDataBase.dbo.DataBaseObjects
    SELECT Name, Type FROM Sys.tables

SELECT INTO:

	SELECT Name, Type 
    INTO MyDataBase.dbo.DataBaseObjects2
    FROM Sys.Tables

So, With the Syntax it is quite clear that INSERT INTO requires the tables should be available in the database whereas in SELECT INTO the table is created by the statement and the data gets loaded to it.

 

Now, you may get questions like both are inserting the data to the tables then what are the differences between the statements? Here is the key difference below,

 

1. INSERT INTO Throws an error if the target table is not available in the database whereas SELECT INTO throws an error if the table already exists in the database.
2. SELECT INTO may not be suitable if we are copying a bulk volume of data from another table and using it to further analysis because the statement just copies the schema, only column names and datatypes, of the source table but not recreates the source indexes on the Target table. So if we try to access the table we end up with performance issues.
3. SELECT INTO is mostly used if we don’t know the schema/structure of the tables in which we want to load data. But if you know the end table structure it is always recommended to create the table with valid schema and indexes and then load the data.

 

Hope you understood! Also, explore other topics in my blog