In SQL Server, it is quite common situation to transfer data from one table to another. This could be between the temporary tables or existing tables. To achieve this, we have 2 options, SELECT INTO & INSERT INTO. 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.
- SELECT INTO will not require any target table to exist in your database for transferring the data from source table. A new table will be created itself, 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. When we use the 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 then 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 sample below :
It will depend 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 may land our-self in the point 2 situation we discussed.
Hope you enjoyed reading it…!!!