MySQL - 插入資料到選擇結果

要透過 Node.js 程式將資料從一個 MySQL 表插入到另一個 MySQL 表中，我們需要使用 **mysql2** 庫的 **query()** 函式執行“INSERT INTO SELECT”語句，如下所示：

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

要透過 Java 程式將資料從一個 MySQL 表插入到另一個 MySQL 表中，我們需要使用 **JDBC** 函式 **executeUpdate()** 執行“INSERT INTO SELECT”語句，如下所示：

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

要透過 Python 程式將資料從一個 MySQL 表插入到另一個 MySQL 表中，我們需要使用 MySQL **Connector/Python** 的 **execute()** 函式執行“INSERT INTO SELECT”語句，如下所示：

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)

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

輸出

產生的輸出如下：

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://: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();
    }
  }
}        

輸出

獲得的輸出如下所示：

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

輸出

以下是上述程式碼的輸出：

Data inserted into new table successfully.