SQL SELECT INTO Statement

The SQL SELECT INTO statement can be used to retrieves data from a table and inserts it to another database table. The SQL SELECT INTO statement used to create backup copies of tables.

SQL> SELECT INTO Syntax 
SELECT field1, field2, field3, INTO destination_table FROM source_table; 

The field names after the SQL SELECT determines which field will be copied, and the destination_table after the SQL INTO keyword specifies to which table to copy those rows. SELECT INTO copies the exact table structure and data into another table specified in the INTO clause.

The fields of the newly created table inherit the fields name and their data types, whether fields can be contain null values or not, and any associated IDENTITY property from the source table. The SELECT INTO statement doesn’t define a primary key for the new table, so you may want to do that manually.

The SQL statement below shows a simple usage of the SQL SELECT INTO command without fields:

SELECT * INTO emp_bak FROM emp 

You don’t need to copy the whole table. If you only want to copy a few fields, you can do so by listing them after the SELECT statement. The example of this is

SELECT ename, job, hiredate, sal INTO emp_bak FROM emp; 

The fields which are to be copied into a new table need not come from just one table. Or from one database. Use the IN clause to insert the copy to another database:

SELECT ename, job, hiredate, sal INTO emp_bak FROM emp;