MySQL INTO Keyword
The INTO
keyword in MySQL is used to specify the destination for data being inserted or selected. It commonly appears in INSERT INTO
statements for adding data to tables and in SELECT ... INTO
statements for storing data into variables.
Usage
The INTO
keyword is employed when you want to direct data into a table or variable. In an INSERT
statement, it helps specify the target table, while in a SELECT ... INTO
statement, it defines the destination for the selected data, typically into variables.
Syntax
INSERT INTO
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Here, INTO table_name
indicates the table where new data will be added.
SELECT INTO Variables
SELECT column1, column2, ...
INTO @variable1, @variable2, ...
FROM existing_table
[WHERE condition];
This syntax stores the results of the query into user-defined variables.
Creating a New Table from a SELECT Query
CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM existing_table
[WHERE condition];
This syntax creates a new table new_table
containing data selected from existing_table
.
Examples
1. Basic Insert
INSERT INTO products (product_name, price)
VALUES ('Laptop', 999.99);
This example inserts a new row into the products
table with specified values for product_name
and price
.
2. Storing Query Result into Variables
SELECT COUNT(*) INTO @total_customers
FROM customers
WHERE country = 'USA';
In this example, the result of the count query is stored in a user-defined variable @total_customers
.
3. Creating a New Table from a SELECT Query
CREATE TABLE recent_orders AS
SELECT customer_id, order_date
FROM orders
WHERE order_date > '2023-01-01';
This syntax creates a new table recent_orders
containing data selected from orders
where the order date is after January 1, 2023.
Tips and Best Practices
- Ensure table existence. When using
INSERT INTO
, make sure the target table already exists. - Use
CREATE TABLE ... AS SELECT ...
for new tables. To create a new table from a select query, prefer usingCREATE TABLE ... AS SELECT ...
instead ofSELECT INTO
. - Verify data types. Match the data types of values with the column definitions in
INSERT INTO
to avoid errors. - Leverage variables for complex queries. Use
SELECT ... INTO
with variables to store results of complex queries for later use. - Handle errors and exceptions. Implement error handling to manage exceptions during data insertion or selection operations.
Note: MySQL differs from other SQL dialects such as SQL Server, where SELECT INTO
is used to create new tables. In MySQL, use CREATE TABLE ... AS SELECT ...
for this purpose.