WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL SELECT INTO Statement



With SQL, you can copy information from one table to another.

The SELECT INTO statement copies data from one table and inserts the data into another new table.


SQL SELECT INTO statement

SELECT INTO statement copies data from one table and inserts data into another new table.

Note:

MySQL database does not support SELECT ... INTO statements, but INSERT INTO ... SELECT

Of course you can use the following statement to copy the table structure and data:

  CREATE TABLE   new table  
AS
SELECT   *   FROM   old table    

SQL SELECT INTO syntax

We can copy all columns into the new table:

SELECT *
INTO newtable [IN externaldb ]
FROM table1;

Or just copy the desired columns into the new table:

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

lamp

Tip: The new table will be created using the column names and types defined in the SELECT statement. You can use the AS clause to apply the new name.



SQL SELECT INTO instance

Create a backup copy of Websites:

SELECT *
INTO WebsitesBackup2016
FROM Websites;

Copy only a few columns into the new table:

SELECT name, url
INTO WebsitesBackup2016
FROM Websites;

Copy only the english website and insert it into the new table:

SELECT *
INTO WebsitesBackup2016
FROM Websites
WHERE country = 'IN';

Copy data from multiple tables into new table:

SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id = access_log.site_id;

Tip: The SELECT INTO statement can be used to create a new, empty table in another mode. Just add a WHERE clause that causes the query to return no data:

SELECT *
INTO newtable
FROM table1
WHERE 1 = 0;