Insertar datos desde otra tabla en MySQL con Insert Into Select

Learning how to correctly use the SQL INSERT INTO SELECT statement is essential to optimize data manipulation in MySQL databases. This statement allows you to fill a new table with data from another existing table, facilitating tasks and improving efficiency.

How to create a table to store investments in MySQL?

Creating a specific table, such as investment, makes it easy to store data related to the capital invested in products. The table is clearly defined with the following attributes:

CREATE  TABLE investment  (  investmentID INT UNSIGNED PRIMARY  KEY AUTO_INCREMENT,  productID INT UNSIGNED NOT  NULL,  investment INT NOT  NULL DEFAULT  0);

It is essential to precisely define each column, correctly establishing data types and restrictions to ensure the integrity and efficiency of the information storage.

What does the Insert Into Select statement consist of?

The INSERT INTO SELECT statement combines two processes into one, filling the newly created table with information extracted directly from another existing table in a simple, fast and orderly manner:

INSERT INTO investment (productID, investment)SELECT productID, stock  * price FROM products;

Important aspects are:

• The VALUES keyword is not used.
• The corresponding columns between the two tables are specified.
• The selected query(SELECT) defines the values to be inserted.

This method guarantees speed and transparency in data transfer.

How to verify and analyze the maximum investments?

Once the data has been inserted, it is easy to perform specific analyses, such as identifying the products with the highest investments by applying descending order and limiting the results:

SELECT  * FROM investmentORDER BY investment DESCLIMIT 10;

To obtain complete information about a specific product highlighted in the analysis, the following is employed:

SELECT  * FROM productsWHERE productID  = 1208;

These analyses serve to clearly identify where the largest investments are concentrated and to decide strategically on the management of capital in specific products.

If you would like to learn more or clarify specific aspects of the INSERT INTO SELECT statement, feel free to leave your comments or questions below.