SQL SELECT INTO Statement

The SELECT INTO Statement

The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.

Syntax

SELECT columnName(s) INTO newTableName FROM originalTableName;

Note: MySQL Server doesn’t support the SELECT INTO. Instead, it supports the syntax INSERT INTO SELECT, which is basically the same thing. The INSERT INTO SELECT syntax is:

INSERT INTO newTableName (columnName(s)) SELECT columnName(s) FROM originalTableName;

We can use REPLACE instead of INSERT to overwrite old rows:

REPLACE INTO newTableName (columnName(s)) SELECT columnName(s) FROM originalTableName;

Make a Backup Copy

The following example makes a backup copy of the "Books" table.

REPLACE INTO Books_Backup (Title, Author, Publisher, Year, Price) SELECT * FROM Books;

The "Books" table:

Title	Author	Publisher	Year	Price
الدورة الدموية	إبن النفيس	دار العلم	1650	230.50
Java 2	L. Johnston	Fast Press	2002	120.99
Linux and Unix	J. Sam	Fast Press	2004	210.99
Operating Systems	M. Stone	Coriolis	2005	167.00
Web Programming	K. Yariv	East Edition	2005	140.60
XML Language	M. Salim	Knowledge Press	2000	90.45

And this SQL:

REPLACE INTO Books_Backup (Title, Author, Publisher, Year, Price) SELECT * FROM Books;

Gives this "Books_Backup" table:

Title	Author	Publisher	Year	Price
الدورة الدموية	إبن النفيس	دار العلم	1650	230.50
Java 2	L. Johnston	Fast Press	2002	120.99
Linux and Unix	J. Sam	Fast Press	2004	210.99
Operating Systems	M. Stone	Coriolis	2005	167.00
Web Programming	K. Yariv	East Edition	2005	140.60
XML Language	M. Salim	Knowledge Press	2000	90.45

Test your SQL Skills

To test your SQL skills click here.