Page 94 - PowerPoint Presentation
P. 94

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
   89   90   91   92   93   94   95   96   97   98   99