Inserting data into one table using other table data

Syntax

INSERT INTO dest_table_name

[column1, column2....columnN]

select column1, column2....columnN

FROM src_table_name

[WHERE condition];

Create an employee table and insert some data into it.

CREATE TABLE employee(

  id int PRIMARY KEY,

  name VARCHAR(25),

  mailId VARCHAR(25),

  dateOfBirth DATETIME

);

INSERT INTO employee VALUES (1, "Hari Krishna", "hari@hari.com", "1989-05-06 07:30:00");

INSERT INTO employee VALUES (2, "Gopi", "gopi@gopi.com", "1987-11-06 17:30:00");

INSERT INTO employee VALUES (3, "PTR", "ptr@ptr.com", "1988-06-06 23:30:00");

INSERT INTO employee VALUES (4, "Rama Krishna", "rama@rama.com", "1988-11-06 3:54:00");

mysql> CREATE TABLE employee(
    ->   id int PRIMARY KEY,
    ->   name VARCHAR(25),
    ->   mailId VARCHAR(25),
    ->   dateOfBirth DATETIME
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO employee VALUES (1, "Hari Krishna", "hari@hari.com", "1989-05-06 07:30:00");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (2, "Gopi", "gopi@gopi.com", "1987-11-06 17:30:00");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO employee VALUES (3, "PTR", "ptr@ptr.com", "1988-06-06 23:30:00");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (4, "Rama Krishna", "rama@rama.com", "1988-11-06 3:54:00");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM employee;
+----+--------------+---------------+---------------------+
| id | name         | mailId        | dateOfBirth         |
+----+--------------+---------------+---------------------+
|  1 | Hari Krishna | hari@hari.com | 1989-05-06 07:30:00 |
|  2 | Gopi         | gopi@gopi.com | 1987-11-06 17:30:00 |
|  3 | PTR          | ptr@ptr.com   | 1988-06-06 23:30:00 |
|  4 | Rama Krishna | rama@rama.com | 1988-11-06 03:54:00 |
+----+--------------+---------------+---------------------+
4 rows in set (0.00 sec)

mysql>

Create employee_name_dob table that contains employee name and dateOfBirth fields of employee table.

CREATE TABLE employee_name_dob(

   myName VARCHAR(25),

   dob DATETIME

);

INSERT INTO employee_name_dob (myName, dob)

SELECT name, dateOfBirth

FROM employee;

mysql> CREATE TABLE employee_name_dob(
    ->    myName VARCHAR(25),
    ->    dob DATETIME
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO employee_name_dob (myName, dob)
    -> SELECT name, dateOfBirth
    -> FROM employee;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM employee_name_dob;
+--------------+---------------------+
| myName       | dob                 |
+--------------+---------------------+
| Hari Krishna | 1989-05-06 07:30:00 |
| Gopi         | 1987-11-06 17:30:00 |
| PTR          | 1988-06-06 23:30:00 |
| Rama Krishna | 1988-11-06 03:54:00 |
+--------------+------------

 

Previous                                                 Next                                                 Home