Sometime we need to copy one table data to another table. Here we show how you can copy one table data to another table using MYSQL Query.
First, we create two tables one table populated with data and other table is empty.
Below are sample tables.
/* First table with data */
--
-- Table structure for table `employees`
--
CREATE TABLE `employees` (
`employee_id` int(11) NOT NULL,
`employee_name` varchar(100) NOT NULL,
`salary` int(11) NOT NULL,
`department` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `employees`
--
INSERT INTO `employees` (`employee_id`, `employee_name`, `salary`, `department`) VALUES
(1, 'Mohan', 10000, 'Sales'),
(2, 'Manoj', 9000, 'Technology'),
(3, 'Manveer', 8000, 'Marketing'),
(4, 'Manpreet', 7000, 'Human Resources'),
(5, 'Manmohan', 6000, 'Administration'),
(6, 'Ravi', 10000, 'Sales'),
(7, 'Rohan', 9000, 'Technology'),
(8, 'Ravindra', 8000, 'Marketing'),
(9, 'Ranveer', 7000, 'Human Resources'),
(10, 'Rahul', 6000, 'Administration'),
(11, 'pappu', 5000, 'Cleaning');
--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
ADD PRIMARY KEY (`employee_id`);
--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
MODIFY `employee_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
COMMIT;
/* Second table without data */
--
-- Table structure for table `employee_new`
--
CREATE TABLE `employee_new` (
`emp_id` int(11) NOT NULL,
`emp_name` varchar(100) NOT NULL,
`emp_salary` varchar(10) NOT NULL,
`emp_department` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for table `employee_new`
--
ALTER TABLE `employee_new`
ADD PRIMARY KEY (`emp_id`);
--
-- AUTO_INCREMENT for table `employee_new`
--
ALTER TABLE `employee_new`
MODIFY `emp_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
First table
Second Table where we copy first table data.
After create both tables, now we copy employees table data to employee_new table.
INSERT INTO `employee_new` SELECT * FROM `employees`;
After Using above query you can see that employees table whole data copy in employee_new table.
Sometime we need to partial data from an existing table to the new one, we use WHERE clause in the SELECT statement.
INSERT INTO `employee_new` SELECT * FROM `employees` WHERE `department` = "Sales";
After Using above query you can see that employees table partial data copy in employee_new table.
I hope this article helps you.
Thanks for visiting Inflay.com .