Save query result in table

Save query result in table

If the destination table already exists, use INSERT ... SELECT to copy the result set into it. For example, if dst_tbl contains an integer column i and a string column s, the following statement copies rows from src_tbl into dst_tbl, assigning column val to i and column name to s:

INSERT INTO dst_tbl (i, s) SELECT val, name FROM src_tbl;

To copy all columns from one table to another

INSERT INTO dst_tbl SELECT * FROM src_tbl;

To copy only certain rows, add a WHERE clause that selects those rows:

INSERT INTO dst_tbl SELECT * FROM src_tbl
WHERE val > 100 AND name LIKE 'A%';

To create additional columns in the destination table besides those selected from the source table, provide appropriate column definitions in the CREATE TABLE part of the statement. The following statement creates id as an AUTO_INCREMENTcolumn in dst_tbl and adds columns a, b, and c from src_tbl:

CREATE TABLE dst_tbl
(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
)

INSERT INTO dst_tbl  SELECT a, b, c FROM src_tbl;

---------------------------------------------------------------------------------------------------------------------

INSERT INIO CDs2
SELECT CDID, CDName, InStock FROM CDs
WHERE Category=’Country’ OR Category=’Rock’.;’

--------------------------------------------------------------------------------------------------------