SQL SELECT INTO Example | SELECT INTO In SQL
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 of column 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 of the columns which we want to copy.
- INTO: This is a keyword that is 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.
#SQL SELECT INTO Statement
The SELECT INTO statement copies the data from one table into the new table.
See the following syntax.
SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition;
When we are 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. 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 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 in the above query if the where clause does not return any data, 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 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 MS Access database extension.
#SELECT INTO Statement: Copy Selected Coloums
If you want to copy the few columns of the table, then 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';
Finally, SQL SELECT INTO Example is over.