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.