MySQL SELECT INTO

The statement SELECT INTO allows you to duplicate a MySQL table, or especially, it copies data from a table to a new table, the problem is that MySQL does not support this statement. To achieve this in MySQL, use the following statement CREATE TABLE SELECT. The syntax is as follows:

CREATE TABLE newtable SELECT * FROM oldtable;

To understand this, let’s first create a table:

CREATE TABLE usersOldTable (
    name VARCHAR(20) NOT NULL,
    age int,
    address VARCHAR(100)
);

 

 
Now let’s insert some records into “usersOldTable” using the INSERT statement.

INSERT INTO usersOldTable (name, age, address) VALUES ('Alex', 20, 'Paris');
INSERT INTO usersOldTable (name, age, address) VALUES ('Emily', 22, 'Lile');
INSERT INTO usersOldTable (name, age, address) VALUES ('Thomas', 15, 'Nantes');

Display all records using the SELECT statement:

SELECT * FROM usersOldTable;

 
Now you can apply the syntax discussed at the beginning to duplicate the MySQL table. The query is as follows:

CREATE TABLE usersNewTable SELECT * FROM usersOldTable;

 
 
Now, check all records in the new table “usersNewTable” using the SELECT statement:

SELECT * FROM usersNewTable;

 
As you can see we have successfully copied data from “usersOldTable” to “usersNewTable”.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More