MySQL – Insert Into Select


MySQL – Insert Into Select


Table of content


”;


Previous

Next

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 −


PHP
NodeJS
Java
Python

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);

To insert data into one MySQL table from another table through a Node.js program, we need to execute the “INSERT INTO SELECT” statement using the query() function of the mysql2 library as follows −


sql = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = ''Male''";
con.query(sql);

To insert data into one MySQL table from another table through a Java program, we need to execute the “INSERT INTO SELECT” statement using the JDBC function executeUpdate() as follows −


String sql = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = "Male"";
st.executeUpdate(sql);

To insert data into one MySQL table from another table through a Python program, we need to execute the “INSERT INTO SELECT” statement using the execute() function of the MySQL Connector/Python as follows −


sql = "INSERT INTO new_tutorials_tbl 
(tutorial_id, tutorial_title, tutorial_author, submission_date) 
SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl"
insert_into_select_query = sql
cursorObj.execute(insert_into_select_query)

Example

Following are the programs −


PHP
NodeJS
Java
Python 


$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
)  



var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("----------------------------------------");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE Agent (ID int NOT NULL,NAME varchar(20) NOT NULL,GENDER varchar(20) NOT NULL,AGE int NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  sql = "INSERT INTO Agent VALUES (1,''Msd'', ''Male'', 21), (2,''Virat'', ''Male'', 23), (3,''Perry'', ''Female'', 24), (4,''Smiti'', ''Female'', 18), (5,''Rose'', ''Female'', 23);"
  con.query(sql);

  sql = "SELECT * FROM Agent;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
    console.log("-------------------------------------------------------");
  });

  sql = "CREATE TABLE Agentdemo(ID int NOT NULL,NAME varchar(50) NOT NULL,GENDER varchar(20) NOT NULL,AGE int NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting required data from one table to another 
  sql = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = ''Male''";
  con.query(sql);

  sql = "SELECT * FROM Agentdemo;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −


Connected!
----------------------------------------
[
  { ID: 1, NAME: ''Msd'', GENDER: ''Male'', AGE: 21 },
  { ID: 2, NAME: ''Virat'', GENDER: ''Male'', AGE: 23 },
  { ID: 3, NAME: ''Perry'', GENDER: ''Female'', AGE: 24 },
  { ID: 4, NAME: ''Smiti'', GENDER: ''Female'', AGE: 18 },
  { ID: 5, NAME: ''Rose'', GENDER: ''Female'', AGE: 23 }
]
-------------------------------------------------------
[
  { ID: 1, NAME: ''Msd'', GENDER: ''Male'', AGE: 21 },
  { ID: 2, NAME: ''Virat'', GENDER: ''Male'', AGE: 23 }
]



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InsertIntoSelect {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM Agent";
            rs = st.executeQuery(sql);
            System.out.println("Agent table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String gender = rs.getString("Gender");
              String age = rs.getString("Age");
              System.out.println("Id: " + id + ", Name: " + name + "Gender: " + gender + ", Age: " + age);
            }
            String sql1 = "SELECT * FROM Agentdemo";
            rs = st.executeQuery(sql1);
            System.out.println("Agentdemo table before after insert into select: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String gender = rs.getString("Gender");
              String age = rs.getString("Age");
              System.out.println("Id: " + id + ", Name: " + name + "Gender: " + gender + ", Age: " + age);
            }
            //now let use the insert into select query
            String sql2 = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = "Male"";
            st.executeUpdate(sql2);
            String sql3 = "SELECT * FROM Agentdemo";
            rs = st.executeQuery(sql3);
            System.out.println("Agentdemo table after after insert into select: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String gender = rs.getString("Gender");
              String age = rs.getString("Age");
              System.out.println("Id: " + id + ", Name: " + name + "Gender: " + gender + ", Age: " + age);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −


Agent table records: 
Id: 1, Name: MsdGender: Male, Age: 21
Id: 2, Name: ViratGender: Male, Age: 23
Id: 3, Name: PerryGender: Female, Age: 24
Id: 4, Name: SmitiGender: Female, Age: 18
Id: 5, Name: RoseGender: Female, Age: 23
Agentdemo table before after insert into select: 
Agentdemo table after after insert into select: 
Id: 1, Name: MsdGender: Male, Age: 21
Id: 2, Name: ViratGender: Male, Age: 23



import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
insert_into_select_query = "INSERT INTO new_tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl"
cursorObj.execute(insert_into_select_query)
connection.commit()
print("Data inserted into new table successfully.")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −


Data inserted into new table successfully.




Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *