How to Copy Rows in the MySQL Database
- Copy Rows in the MySQL Database
-
Use the
INSERT&SELECTStatements to Copy Rows From One Table to Another in MySQL Database -
Use the
INSERTandSELECTStatements to Copy Rows Within the Same Table With an Auto-Increment ID -
Use the
INSERT&SELECTStatements to Copy Rows Within the Same Table With a Custom ID
Today’s topic is about copying rows in the MySQL database. We will learn how to copy one row and paste it within the same table with an auto-increment ID and custom ID.
We will also understand how to copy multiple fields from multiple rows of one table and paste them into another table.
Copy Rows in the MySQL Database
Copying rows is beneficial when requiring two or multiple exact columns in another table. We can copy a row (a record) from one table to another rather than insert them manually.
We can use different approaches to copy rows in the MySQL database. First, to learn these methods, let’s create a test database containing two tables, tb_students and tb_attendance.
Example 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`));
Use the INSERT & SELECT Statements to Copy Rows From One Table to Another in MySQL Database
We have to insert data into one table to copy rows from that table to another. For that, we populate the table named tb_students as follows.
Example 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, copy the data from the tb_students table and insert it into the tb_attendace table. Notice that we are not copying all data but the particular fields to populate the tb_attendance 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 INSERT and SELECT Statements to Copy Rows Within the Same Table With an Auto-Increment ID
For this section, we copy a row from the tb_students table with four records, insert it with an auto-increment ID, and update the value for the PHONE and CITY attributes.
Example 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 INSERT & SELECT Statements to Copy Rows Within the Same Table With a Custom ID
If we don’t want the auto-increment ID but a custom ID in the tb_students table, we can write the column name in the INSERT statement and its value in the SELECT statement as follows.
Example 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:
