Latest web development tutorials

SQL SELECT INTO statement

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

SELECT INTO statement to copy data from a table, and then insert the data into another new table.

SQL SELECT INTO statement

SELECT INTO statement to copy data from a table, and then insert the data into another new table.

SQL SELECT INTO Syntax

We can replicate all columns into the new table:

SELECT *
INTO newtable [IN externaldb ]
FROM table1;

Or just copy the desired columns into a new table:

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

lamp

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


SQL SELECT INTO examples

Customers create a backup copy:

SELECT *
INTO WebsitesBackup2016
FROM Websites;

Please use the IN clause to copy the table to another database:

SELECT *
INTO WebsitesBackup2016 IN 'Backup.mdb'
FROM Websites;

Copy only some of the columns into the new table:

SELECT name, url
INTO WebsitesBackup2016
FROM Websites;

Copy only Chinese website into the new table:

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

Copy data from multiple tables into a 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: SELECT INTO statement can be used to create a new, empty table by another mode. Just add prompted no data is returned to the WHERE clause of the query:

SELECT *
INTO newtable
FROM table1
WHERE 1=0;