The SELECT INTO statement copies the data from one table into the new table.
SQL SELECT INTO Statement
SQL SELECT INTO is used to create a table from an existing table by copying the contents of the current table to the new table. The critical point to note here is that the column and data type must be the same. SELECT INTO copies data from one table into a new table. SELECT INTO creates a new table located in the default filegroup.
See the following syntax.
Select column(s) INTO new_table from old_table_name where(condition).
- Column(s): Name the columns we want to copy.
- INTO: This is a keyword used to perform the copy operation.
- New_table: Name of the new table which will be created.
- Old_table_name: Name of the old table from where we are copying.
- Where(condition): This is used for imposing conditions into the select statement.
When using the select into a statement, the new_table should not exist. If it does already, then the user must have to drop it; otherwise, it will raise an error.
Let’s clear the above syntax with an example.
Consider table: (Students).
See the following query.
Copying all the contents to the new table.
Select * INTO new_students from students;
In this query, all the contents of the students’ table will be copied to the new_students table.
Copying a new table to the database.
Select * INTO new_students IN ‘sample.mdb’ FROM students;
In this, all the contents of the students’ table will be copied to the new_students table later, which will be copied to another database named sample. Finally, the new_students table will be stored in the MS Access database extension.
For Copying Selected Columns.
Select Roll, Name INTO new_students from students;
In this Roll and Name columns are copied to the new table new_students.
For copying specific rows from columns.
Select Roll, Name INTO new_students from students where name like ‘S%’;
In this query, Shubh, Shouvik, and Shivam under the column named Name are copied to a new table named new_students. We have used the wildcard characters.
If the where clause does not return any data in the above query, then an empty new table is created.
SELECT INTO creates a new table located in the default filegroup.
SELECT INTO Statement with IN Clause
One can use the SELECT INTO command in conjunction with the IN clause for copying the table into another database. See the following example.
SELECT * INTO new_students IN 'Backup.db' FROM students;
This will create a copy of the new_students table in the MS Access database extension.
SELECT INTO Statement: Copy Selected Columns
If you want to copy the few columns of the table, below is an example.
SELECT Roll, Name INTO new_students FROM students
SELECT INTO Statement: Select Specific Rows
SQL TOP CLAUSE Output
All these three commands would give the following results in the respective database.
SELECT name, city INTO new_students FROM students WHERE city='Patna';
That’s it for this tutorial.