Prev Next
SELECT INTO statement in SQL is used to select the data from a table and insert the selected data in another table. I.e. it copies the data from one table to another table. Either we can copy all data or partial data from one table to another. Syntax for SQL SELECT INTO statement is given below.
SQL Syntax for SELECT…INTO statement:
| Syntax for SELECT…INTO statement
(Copying within same database tables) |
SELECT * INTO target_table_name FROM source_table_name; |
| Syntax for SELECT…INTO statement
(Copying between 2 different database tables) |
SELECT * INTO target_table_name [IN external_database_name] FROM source_table_name; |
Note:
- We can filter the records using WHERE clause before copying from one table to another. If we do not use WHERE clause, all records from source table will be copied to target table.
- Also, please make sure both source and target table schemas (i.e. all columns’ property) are same. Else, data might be lost or truncated.
Example1: how to use the SQL SELECT…INTO statement:
Please consider the following tables with a few records in source table (student) and no records in target table (student_bkup) as given below.
Source table:
Source table name (for example): student
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows
Target table:
Target table name (for example): student_bkup
Column names in this table: Student_ID, Student_name, City and Age
Available records: 0 rows
Note:
This table should be created or available with the same table schema for table name ‘student’.
- To copy all data from the source table to target table, please execute below SQL query. This query will fetch all records available in the source table and will insert these data in the target table.
SQL query:
SELECT *
INTO student_bkup
from student;
SQL query Output in target table (student_bkup):
After executing above SQL query, all records from source table will be copied into target table. Please run following SQL statement to know currently existing records in the target table.
SQL query: SELECT * FROM student_bkup;
Example2: how to use the SQL SELECT…INTO statement with a WHERE clause:
To copy only particular records from the source table to target table, please execute below query.
SQL query:
SELECT *
INTO student_bkup
from student
WHERE Student_ID>2;
SQL query Output in target table (student_bkup):
- Our assumption is, target table (student_bkup) is empty before running the above SQL query.
- After executing above SQL query, records from source table will be copied into target table when student_ID is greater than 2. Please run following SQL statement to know currently existing records in the target table.
SQL query: SELECT * FROM student_bkup;
