MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

 Insert Into Select
The MySQL Insert Into Select Statement
In MySQL, the INSERT INTO... SELECT statement is used to add/insert one or more rows from an existing table to target table.
This statement is a combination of two different statements: INSERT INTO and SELECT.
The MySQL INSERT INTO statement is a commonly used command in database management and it requires only the name of the table and the values to be inserted into a table. However, it is important to ensure that the data being inserted matches the structure and data types of the table columns.
The SELECT statement is used to fetch data from an existing database table.
When the above mentioned statements are used together, the SELECT statement first fetches the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures).
Syntax
Following is the syntax for using insert into select statement 
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ... 
FROM table1
WHERE condition;
Following are some important points that we have to consider before we execute the below queries −
In the database where we are going to insert data, a table must already exist.
Both the source and target tables must match its structure.
Example
First of all, let us create a table named CUSTOMERS using the following query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
The following query inserts 7 records into the above created table 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
Execute the following query to retrieve all the records from CUSTOMERS table 
Select * From CUSTOMERS;
Following is the CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Inserting Required Data from one Table to Another Table
We may come across some instances where we only want to add small number of records to another table. This can be achieved by using a WHERE clause to select all the number of rows that the query returned.
Example
Before that, let us create a another table named CUSTOMERS_copy with similar structure of previously created CUSTOMERS table 
CREATE TABLE CUSTOMERS_copy (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
In the following query, we are trying to fetch the records from the CUSTOMERS table and insert them into the CUSTOMERS_copy table.
INSERT INTO CUSTOMERS_copy (ID, NAME, AGE, ADDRESS, SALARY)
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
WHERE AGE >= 25;
Output
The output for the program above is produced as given below 
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0	
Verification
To confirm if the records from the 'CUSTOMERS' table, where the age is 25 or older, have been inserted to the target table 'CUSTOMERS_copy', execute the following query 
SELECT * FROM CUSTOMERS_copy;
Following are the records whose age is 25 or older 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
Inserting the rows with LIMIT
Using the MySQL LIMIT clause, we can specify the number of rows from the query that should be added to the target table.
Example
Before proceeding further, let us first truncate all rows in the CUSTOMERS_copy table using the following query 
TRUNCATE TABLE CUSTOMERS_copy;
Now, we are going to insert the top 3 records from CUSTOMERS table sorted by their AGE using the LIMIT clause 
INSERT INTO CUSTOMERS_copy (ID, NAME, AGE, ADDRESS, SALARY)
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
ORDER BY AGE LIMIT 3;
Output
The output for the program above is produced as given below 
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
Verification
Execute the following query to verify whether the records are reflected in the CUSTOMERS_copy table or not 
SELECT * FROM CUSTOMERS_copy;
Following are the records 
ID
NAME
AGE
ADDRESS
SALARY
3
Kaushik
23
Kota
2000.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Inserting All Columns from one Table to Another Table
We can also insert every column from one to another table. To do so, following is the syntax 
INSERT INTO table2
SELECT * FROM table1
Before inserting all the records, first truncate all rows in the CUSTOMERS_copy table by using the statement 
TRUNCATE TABLE CUSTOMERS_copy;
In the following query, we are trying to add all the columns from the CUSTOMERS table to the CUSTOMERS_copy table
INSERT INTO CUSTOMERS_copy SELECT * FROM CUSTOMERS;
Output
All the columns have been inserted without any errors.
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
Verification
We can verify whether the changes are reflected in a CUSTOMERS_copy table by retrieving its contents using the SELECT statement.
SELECT * FROM CUSTOMERS_copy;
Following is the CUSTOMERS_copy table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
INSERT INTO SELECT Using a Client Program
Besides using MySQL queries to perform the INSERT INTO ... SELECT statement, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.
Syntax
Following are the syntaxes of this operation in various programming languages 
PHPNodeJSJavaPython
To insert data into one MySQL table from another table through a PHP program, we need to execute the "INSERT INTO SELECT" statement using the mysqli function query() as follows 
$sql = "INSERT INTO new_tutorials_tbl SELECT * FROM tutorials_tbl WHERE tutorial_id = 2";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$q = "SELECT * FROM new_tutorials_tbl";
if($res = $mysqli->query($q)){
   printf("Select query executed successfully..!\n");
   printf("The table 'new_tutorials_tbl' records before insert into select query: \n");
   while($r = mysqli_fetch_array($res)){
       print_r ($r);
   }
}
$sql = "INSERT INTO new_tutorials_tbl SELECT * FROM tutorials_tbl WHERE tutorial_id = 2";
if($result = $mysqli->query($sql)){
   printf("Insert into select query executed successfully..! \n");
}
$q = "SELECT * FROM new_tutorials_tbl";
if($res = $mysqli->query($q)){
   printf("Select query executed successfully..!\n");
   printf("The table 'new_tutorials_tbl' records after insert into select query: \n");
   while($r = mysqli_fetch_array($res)){
       print_r ($r);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows 
Select query executed successfully..!
The table 'new_tutorials_tbl' records before insert into select query:
Array
(
    [0] => 1
    [tutorial_id] => 1
    [1] => Java Tutorial
    [tutorial_title] => Java Tutorial
    [2] => new_author
    [tutorial_author] => new_author
    [3] =>
    [submission_date] =>
)
Insert into select query executed successfully..!
Select query executed successfully..!
The table 'new_tutorials_tbl' records after insert into select query:
Array
(
    [0] => 1
    [tutorial_id] => 1
    [1] => Java Tutorial
    [tutorial_title] => Java Tutorial
    [2] => new_author
    [tutorial_author] => new_author
    [3] =>
    [submission_date] =>
)
Array
(
    [0] => 2
    [tutorial_id] => 2
    [1] => PHP Tut
    [tutorial_title] => PHP Tut
    [2] => unknown2
    [tutorial_author] => unknown2
    [3] => 2023-08-12
    [submission_date] => 2023-08-12
)  
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Insert Into Select

The MySQL Insert Into Select Statement

Syntax

Example

Inserting Required Data from one Table to Another Table

Example

Output

Verification

Inserting the rows with LIMIT

Example

Output

Verification

Inserting All Columns from one Table to Another Table

Output

Verification

INSERT INTO SELECT Using a Client Program

Syntax

Example

Output