Skip to content

Oracle INSERT INTO SELECT

In this post, we will see how to insert a data into a table with the result of select statement. Sometimes we want to move the data from one table to another, in that case we use insert into select statement.

Syntax

INSERT INTO table
(column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n
FROM source_table
[WHERE conditions];

Let’s use the below STUDENT_MARKS table to understand the topic,

Example-1

INSERT INTO STUDENT_MARKS_COPY SELECT * FROM STUDENT_MARKS;

The above query will insert all the records from the STUDENT_MARKS table into the STUDENT_MARKS_COPY table. This query will helpful in the situation when we need the backup of our data or we have to do anything with our data but we don’t want to disturb our original data.

Example-2

INSERT INTO STUDENT_PASSED (STUD_ID, STUD_NAME, RESULT) 
SELECT STUD_ID, STUD_NAME,
CASE WHEN JAVA < 60 OR JAVA_LAB < 60 OR SQL < 60 THEN 'FAIL'
ELSE 'PASS' END AS RESULT 
FROM STUDENT_MARKS;

Result

In the above query we have defined the value for Result column based on the mark scored by the student and inserted into the table STUDENT_PASSED. Like wise we can use the select to insert the data into the new table.

Happy Learning 🙂

See also  List active connection details in MariaDB

Leave a Reply

Your email address will not be published. Required fields are marked *

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