SQL INSERT INTO SELECT

If you are here, you surely know how to perform an INSERT SQL without problems, at least in the simple and classic way, and you are wondering how to perform inserts in SQL from a SELECT.

use INSERT SQL and SELECT together It is possible, and they also work wonderfully. It is a super practical tool to carry out migration tasks from one table to another, such as a history or a table that starts from another.

Content

  • INSERT query with SELECT
    • Example of INSERT from SELECT
    • INSERT INTO SELECT example without specifying columns
  • Video of INSERT INTO SELECT
  • conclusions

INSERT query with SELECT

Create one INSERT query with SELECT It’s easy, you just have to know the structure properly.

First we will compose our INSERT SQL query and then we will continue with a normal select.

Let’s look at an example of this “INSERT SELECT” for clarity.

Example of INSERT from SELECT

For the example I will use 2 tables created with a CREATE SQL like the following:

CREATE tabla {
	id  INT(11) NOT NULL AUTO_INCREMENT,
	nombre VARCHAR(25),
	apellidos VARCHAR(200),
}

CREATE tabla2 {
	id  INT(11) NOT NULL AUTO_INCREMENT,
	nombre_copia VARCHAR(25),
	apellidos_copia VARCHAR(200),
}

Once you know what the tables for the example are like, we are going to insert the information from the table “table” into the table “table2”. Specifically, I am going to pass the information from the first and last names columns.

To do this I will use, of course, an INSERT and a SELECT.

INSERT INTO tabla2 ( nombre_copia , nombre_copia )
SELECT nombre , apellidos 
FROM tabla

INSERT INTO SELECT example without specifying columns

You should know that there is a faster way to carry out the above query as long as one condition is met: that the 2 tables involved in the query have the columns in the same order.

What do you mean in the same order? Let’s see it more clearly with another example

INSERT INTO tabla2 
SELECT * FROM tabla

In this query you can see how I have not specified columns of any type, what will happen then? Well, since I know that I have defined the 2 tables with the same columns and in the same order (their values ​​and types in the CREATE SQL above), the database will automatically insert the values ​​of the SELECT SQL columns where it touches.

What are the clues in this example?

  1. In the INSERT I specify in which columns I am going to insert the SQL data.
  2. In the SELECT I specify which columns I want to obtain. Also, very IMPORTANT, the order of the columns in this SELECT matches those specified in the INSERT.
  3. The column types are the same.
  4. It is not necessary that the columns of both tables have the same name, only that the data “fit inside” the target table. To do this, the types of the columns and their ability. This means, for example, that I am not going to put a TEXT of more than 255 characters in a VARCHAR.

Video of INSERT INTO SELECT

To leave this tutorial complete, and in case you still have some doubts, I would like to provide an explanatory video that makes it clear how to use this query INSERT INTO SELECT.

The video is a spoken explanation and step by step through the windows console and MySQL.

conclusions

Is it clear to you how to use this query in SQL? I really hope so.

Now I would really appreciate it if you would leave some input for those who come after you and help us to continue publishing useful content for all SQL programmers.

Thank you!

Leave a Reply