JIYIK CN >

Today's topic is about duplicating rows in MySQL database. We will learn how to copy a row and paste it into the same table with auto increment ID and custom ID.

We will also see how to copy multiple fields from multiple rows of one table and paste them into another table.

Copying rows in a MySQL database

Copying rows is useful when you need two or more exact columns from another table. We can copy a row (a record) from one table to another instead of inserting them manually.

We can use different methods to copy rows in a MySQL database. First, to learn these methods, let's create a database with two tables tb_studentsand .tb_attendancetest

Sample code:

# create a database
CREATE SCHEMA `test`;

# create students table
CREATE TABLE `test`.`tb_students` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `FIRSTNAME` VARCHAR(45) NOT NULL,
  `LASTNAME` VARCHAR(45) NOT NULL,
  `DEPARTMENT` VARCHAR(45) NOT NULL,
  `GENDER` VARCHAR(10) NOT NULL,
  `PHONE` BIGINT NOT NULL,
  `CITY` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`));

# create attendance table
CREATE TABLE `test`.`tb_attendance` (
  `ID` INT NOT NULL,
  `FIRSTNAME` VARCHAR(45) NOT NULL,
  `LASTNAME` VARCHAR(45) NOT NULL,
  `GENDER` VARCHAR(45) NOT NULL,
  `DEPT` VARCHAR(45) NOT NULL,
  `ATTENDANCE` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`));

INSERTUse the and statements in MySQL database SELECTto copy rows from one table to another

We must insert data into one table before we can copy rows from that table into another. To do this, we populate tb_studentsa table named as follows.

Sample code:

#insert data into the table
INSERT INTO `test`.`tb_students`
(FIRSTNAME, LASTNAME, DEPARTMENT, GENDER, PHONE, CITY)
VALUE
('Mehvish', 'Ashiq', 'Computer Science', 'Female', '1234567890', 'Lahore'),
('Thomas', 'Christopher', 'Physics', 'Male', '2546317908', 'Miami'),
('Daniel', 'James', 'Business Administration', 'Male', '7854123690', 'Texas'),
('Saira', 'Kethy', 'History', 'Female', '3254169870', 'Michigan');

#display table data
SELECT * FROM test.tb_students;

Output:

Now, tb_studentscopy the data from the table and insert it tb_attendaceinto the table. Note that we are not copying all the data, but specific fields to populate tb_attendancethe table.

#insert data into the table
INSERT INTO `test`.`tb_attendance`
(ID, FIRSTNAME, LASTNAME, GENDER, DEPT)
SELECT ID, FIRSTNAME, LASTNAME, GENDER, DEPARTMENT
FROM `test`.`tb_students`;

#display table data
SELECT * FROM test.tb_attendance;

Output:

Use the INSERTand SELECTstatements to copy rows in the same table with auto-increment IDs

In this section, we tb_studentscopy a row from the table containing four records, insert it with an auto-increment ID, and update the values ​​of the PHONEand CITYattributes.

Sample code:

INSERT INTO `test`.`tb_students`
(FIRSTNAME, LASTNAME, DEPARTMENT, GENDER, PHONE, CITY)
SELECT FIRSTNAME, LASTNAME, DEPARTMENT, GENDER, '2564138790', 'Dubai'
FROM `test`.`tb_students`
WHERE `test`.`tb_students`.ID = 1;

SELECT * FROM `test`.`tb_students`;

Output:

Use the INSERTand SELECTstatements to copy rows from the same table with a custom ID

If we do not want tb_studentsto use auto-increment ID in the table but customize the ID, we can INSERTwrite the column name in the statement and SELECTits value in the statement as shown below.

Sample code:

INSERT INTO `test`.`tb_students`
(ID, FIRSTNAME, LASTNAME, DEPARTMENT, GENDER, PHONE, CITY)
SELECT 10,FIRSTNAME, LASTNAME, DEPARTMENT, GENDER, '2564138790', 'Dubai'
FROM `test`.`tb_students`
WHERE `test`.`tb_students`.ID = 1;

SELECT * FROM `test`.`tb_students`;

Output: