AppDividend
Latest Code Tutorials

SQL SELECT INTO Statement Tutorial With Example

0

SQL SELECT INTO Statement Tutorial With Example is today’s topic. The SQL SELECT INTO STATEMENT 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. See the following syntax.

Select column(s) 
INTO new_table 
from old_table_name where(condition).

#PARAMETERS

  1. Column(s): Name of the columns which we want to copy.
  2. INTO: This is a keyword which is used to perform the copy operation.
  3. New_table: Name of the new table which will be created.
  4. Old_table_name: Name of the old table from where we are copying.
  5. 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. You can also see the following syntax.

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

NOTE:

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 example.

Consider table: (Students).

Roll Name City
1 Rohit Patna
2 Shouvik Jalandhar
3 Shubh Kolkata
4 Karan Allahabad
5 Shivam Palampur
6 Karan Dhulian

 

See the following query.

Copying all the contents to 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.

#QUERY 2

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 MS Access database extension.

#QUERY 3

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.

#QUERY 4

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

You can use WHERE clause to select specific rows of a table. Suppose in the above example, and one wants to display students name & students roll of students where city = Patna.

#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 Statement Tutorial With Example is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.