When To Use SELECT INTO Vs INSERT INTO In SQL Server

Learn what is the difference between Select Into and Insert Into SQL queries and when to use one over other.

In SQL Server, developers often move data from one database table to another. This could be either between the temporary tables or existing tables.

To achieve this, we have 2 options, use a SELECT INTO SQL query or use an INSERT INTO SQL query. Both provide the same functionality, but there are two important differences between these two, which may affect your decision about which one to use.

 

Let's discuss these differences one by one.

 

1. The first difference is that SELECT INTO does not requires the target table to exist in your database for transferring the data from source table. A new table will be created automatically, when this command is used.

On the other hand, INSERT INTO requires a target table to exist, before data can be transferred from source to target table.



As seen above, INSERT INTO will require us to create a target table and then insert the data. Also, if we try to execute the SELECT INTO for an existing table, it will not work as it will try to create another table with same name, which will result in error.
  
2. The second difference is related to the structure of the target tables. As we discussed above, when we use SELECT INTO statement, it creates the structure of the table automatically. The problem is that the new table is created with the same structure, which the source table has. This can become a problem when we try to insert the data. Let's discuss with an example

We have a source table, with a VARCHAR column of size 50. When we use SELECT INTO, the target table generated will also have the VARCHAR column with size 50. So if you try to insert data in the VARCHAR column, which is more than the size 50, it will result in error String or binary data would be truncated. Of-course this would have been the error in normal scenarios also, but this error is something which could affect your choice to select type of command you should use. See the example below:

Which one to use?

It entirely depends on our requirements, which option to go for. Important thing to note is that the same issue also occurs with the use of temporary tables. So we have to be very careful about which option to select while working them with temporary tables, as we normally use quick code practices and select the quickest way to do it and this may result in situation we discussed in point 2.

Learn more

 

Here is a detailed tutorial, Different Methods of SQL Queries to Insert Data Into Tables