Page 94 - PowerPoint Presentation

CAVITE STATE UNIVERSITY
T3 CAMPUS
Department of Information Technology DCIT 55 – Advance Database System

MySQL INSERT INTO (to copy records from one table to another table)
We can use the SELECT statement with INSERT INTO statement to copy records (rows) from
one table and insert them into another table. The use of this statement is similar to that of
INSERT INTO statement. The difference is that the SELECT statement is used here to select
data from a different table. The different ways of using INSERT INTO SELECT statement are
shown below:
INSERT INTO newtable SELECT*FROM oldtable WHERE condition;

The basic syntax to copy only some columns from one table into another table is as follows:
INSERT INTO newtable (column1, column2) SELECT column1, column2 FROM oldtable
WHERE condition;

Example: If you want to copy all columns from cus_tbl table to cust_tbl table, then the INSERT
INTO SELECT statement would be as shown below:
INSERT INTO cus_tbl SELECT*FROM cust_tbl;

NOTE: Before you use this statement, you should first create a new table that has also the
same fields with the old table.

Example: If you want to copy only some columns such as cus_id and cus_fname from cus_tbl
to cust_tbl, then the INSERT INTO SELECT statement would be as shown below:
INSERT INTO cust_tbl SELECT cus_id, cus_fname FROM cus_tbl;

NOTE: Before you use this statement, you should first create a new table that has also the
same fields with the old table. But if you will only copy the cus_id and cus_fname, don’t include
the cus_fname in the create table process.

Example: If you want to copy only all columns from cus_tbl table to cust_tbl table whose id is
‘101’, then the INSERT INTO SELECT statement would be as shown below:
INSERT INTO cust_tbl SELECT*FROM cus_tbl WHERE cus_id=101;

If you want to insert multiple records(rows) of same table, then the INSERT INTO SELECT
statement would be as shown below:
INSERT INTO cus_tbl SELECT*FROM cust_tbl WHERE cus_id IN (101, 102);

Page | 10