SQL SELECT INTO Tutorial
The SELECT INTO statement copies data from one table and inserts it into a new table.
The number of columns and data type of column must be same.
Syntax of a SELECT INTO STATEMENT
SELECT column_name(s)
INTO new_table
FROM table_name;
Note: WHERE condition is optional to use along with SELECT INTO Clause.
SQL SELECT INTO STATEMENT Example:
Database table “Employee”
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1001 | Henry | 54 | Male | New York | 100000 |
| 1002 | Tina | 36 | Female | Moscow | 80000 |
| 1003 | John | 24 | Male | London | 40000 |
| 1004 | Mile | 31 | Male | London | 70000 |
| 1005 | Tara | 26 | Female | Moscow | 50000 |
| 1006 | Sohpie | 29 | Female | London | 60000 |
Below is an example of a sql statement to create a copy of “Employee” table, use of * selects all rows.
If you want to copy certain columns then declare the column name(s)
The user can alternatively specify number of rows or % of rows
SELECT *
INTO Copy_Employee
FROM Employee;
Command will create a table similar to “Employee” table. This SELECT INTO statement is often used for creating a backup copy of a table.
SELECT INTO Statement with IN Clause
One can use SELECT INTO cmmand in conjunction with the IN clause for copying the table into another database.
SELECT *
INTO Copy_Employee IN 'Backup.mdb'
FROM Employee;
This will create a copy of Employee table in MS Access database extension.
SELECT INTO Statement: Copy Selected Coloums
If one wants to copy only a few columns of table, then below is an example.
SELECT EmployeeID, EmployeeName
INTO Copy_Employee
FROM Employee;
SELECT INTO Statement: Select Specific Rows
One can also use WHERE clause to select specific rows of a table. Suppose in the above example one wants to display Employee ID & Employee Name of all male employees.
SQL TOP CLAUSE Output
All these three commands would give the following results in respective database.
SELECT EmployeeID, EmployeeName
INTO Copy_Employee
FROM Employee
WHERE Gender='Male';
Note: If the condition in the WHERE clause returns no data, then an empty new table is created.
