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.
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,
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.
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;
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 🙂