INSERT INTO vs SELECT INTO in SQL Server


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.

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

Step 4As 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 :

INSERT INTO vs SELECT INTO in SQL Server

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…!!!

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server and tagged . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s