{"id":16113,"date":"2020-05-22T22:37:06","date_gmt":"2020-05-22T22:37:06","guid":{"rendered":"https:\/\/ittutorial.org\/?p=16113"},"modified":"2020-11-11T19:12:41","modified_gmt":"2020-11-11T19:12:41","slug":"insert-into-select-statement-in-oracle-sql-oracle-sql-tutorials-8","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/insert-into-select-statement-in-oracle-sql-oracle-sql-tutorials-8\/","title":{"rendered":"Insert Into Select Statement in Oracle SQL | Oracle SQL Tutorials -8"},"content":{"rendered":"<p align=\"left\">I will explain Insert Into Select Statement in Oracle SQL in this post of Oracle SQL Tutorial series.<\/p>\n<p align=\"left\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-16079\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial.png\" alt=\"\" width=\"387\" height=\"311\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial.png 387w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial-300x241.png 300w\" sizes=\"auto, (max-width: 387px) 100vw, 387px\" \/><\/p>\n<p align=\"left\"><!--more--><\/p>\n<p align=\"left\">\n<h2 align=\"left\"><span style=\"color: #ff0000;\">Insert Into Select Statement in Oracle SQL<\/span><\/h2>\n<p align=\"left\"><span style=\"color: #ff0000;\"><strong>Read the previous post of this tutorial series before this.<\/strong><\/span><\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"8imrNmjbSV\"><p><a href=\"https:\/\/ittutorial.org\/insert-into-in-oracle-sql-oracle-sql-tutorials-7\/\">Insert Into in Oracle SQL | Oracle SQL Tutorials -7<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Insert Into in Oracle SQL | Oracle SQL Tutorials -7&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/insert-into-in-oracle-sql-oracle-sql-tutorials-7\/embed\/#?secret=819KZNJZpc#?secret=8imrNmjbSV\" data-secret=\"8imrNmjbSV\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"color: #ff0000;\"> INSERT INTO SELECT Statement<\/span><\/h3>\n<p>You can copy data from one table to another table using The INSERT INTO SELECT statement easily. But To use The INSERT INTO SELECT statement, source and target tables match.<\/p>\n<p>When you use The INSERT INTO SELECT statement , existing records in the target table remain the same, and new records are appended the target table.<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>INSERT DATA FROM ANOTHER TABLE<\/strong><\/span><\/p>\n<ul>\n<li>use sub-query to copy data from another table.<\/li>\n<li>The VALUES statement is not used.<\/li>\n<li>The column count in the subquery and INSERT statement must be same.<\/li>\n<li>All the records that came from the subquery is inserted to table.<\/li>\n<li>Let\u2019s add a record to hr.jobs table from hr.departments.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>INSERT INTO SELECT Syntax<\/strong><\/span><\/p>\n<p>The INSERT INTO SELECT statement syntax is as follows.<\/p>\n<p>&nbsp;<\/p>\n<pre><span class=\"sqlcolor\"><span class=\"sqlkeywordcolor\">INSERT<\/span>\u00a0<span class=\"sqlkeywordcolor\">INTO<\/span>\u00a0<em>table2 <\/em><span class=\"sqlkeywordcolor\">SELECT<\/span>\u00a0*\u00a0<span class=\"sqlkeywordcolor\">FROM<\/span>\u00a0<em>table1;<\/em><\/span><\/pre>\n<pre class=\"w3-code notranslate sqlHigh\"><span class=\"sqlcolor\"><span class=\"sqlkeywordcolor\">INSERT<\/span>\u00a0<span class=\"sqlkeywordcolor\">INTO<\/span>\u00a0<em>table2 <\/em><span class=\"sqlkeywordcolor\">SELECT<\/span>\u00a0*\u00a0<span class=\"sqlkeywordcolor\">FROM<\/span>\u00a0<em>table1 <\/em><span class=\"sqlkeywordcolor\">WHERE<\/span>\u00a0<em>condition<\/em>;<\/span><\/pre>\n<pre><span class=\"sqlkeywordcolor\">INSERT<\/span>\u00a0<span class=\"sqlkeywordcolor\">INTO<\/span>\u00a0<em>table2\u00a0<\/em>(<em>column1<\/em>,\u00a0<em>column2<\/em>,\u00a0<em>column3<\/em>, ...)\u00a0 <span class=\"sqlkeywordcolor\">SELECT<\/span>\u00a0<em>column1<\/em>,\u00a0<em>column2<\/em>,\u00a0<em>column3<\/em>, ... <span class=\"sqlkeywordcolor\">FROM<\/span>\u00a0<em>table1<\/em>;\r\n\r\n<span class=\"sqlkeywordcolor\">INSERT<\/span>\u00a0<span class=\"sqlkeywordcolor\">INTO<\/span>\u00a0<em>table2\u00a0<\/em>(<em>column1<\/em>,\u00a0<em>column2<\/em>,\u00a0<em>column3<\/em>, ...)\u00a0 <span class=\"sqlkeywordcolor\">SELECT<\/span>\u00a0<em>column1<\/em>,\u00a0<em>column2<\/em>,\u00a0<em>column3<\/em>, ... <span class=\"sqlkeywordcolor\">FROM<\/span>\u00a0<em>table1 <\/em><span class=\"sqlkeywordcolor\">WHERE<\/span>\u00a0<em>condition<\/em>;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>You can use the INSERT INTO SELECT statement as follows in the example.<\/p>\n<pre>INSERT INTO hr.jobs (job_id,\r\n                     job_title,\r\n                     min_salary,\r\n                     max_salary)\r\n  SELECT 'COMP_ENG',\r\n          department_name,\r\n          30000,\r\n          80000\r\n  FROM hr.departments\r\n  WHERE department_id = 11;\r\n\r\nCOMMIT;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>You can run the following insert into select statement, then query the table as follows.<\/p>\n<pre>SQL&gt; INSERT INTO hr.jobs (job_id, job_title, min_salary, max_salary) SELECT 'COMP_ENG', department_name, 30000, 80000 FROM hr.departments WHERE department_id = 11;\r\n\r\n1 row created.\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.\r\n\r\nSQL&gt; \r\n\r\nSQL&gt; select * from hr.jobs;\r\n\r\nJOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY\r\n---------- ----------------------------------- ---------- ----------\r\nAD_PRES    President                                20080      40000\r\nAD_VP      Administration Vice President            15000      30000\r\nAD_ASST    Administration Assistant                  3000       6000\r\nFI_MGR     Finance Manager                           8200      16000\r\nFI_ACCOUNT Accountant                                4200       9000\r\nAC_MGR     Accounting Manager                        8200      16000\r\nAC_ACCOUNT Public Accountant                         4200       9000\r\nSA_MAN     Sales Manager                            10000      20080\r\nSA_REP     Sales Representative                      6000      12008\r\nPU_MAN     Purchasing Manager                        8000      15000\r\nPU_CLERK   Purchasing Clerk                          2500       5500\r\n\r\nJOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY\r\n---------- ----------------------------------- ---------- ----------\r\nST_MAN     Stock Manager                             5500       8500\r\nST_CLERK   Stock Clerk                               2008       5000\r\nSH_CLERK   Shipping Clerk                            2500       5500\r\nIT_PROG    Programmer                                4000      10000\r\nMK_MAN     Marketing Manager                         9000      15000\r\nMK_REP     Marketing Representative                  4000       9000\r\nHR_REP     Human Resources Representative            4000       9000\r\nPR_REP     Public Relations Representative           4500      10500\r\nCOMP_ENG   Computer Engineering                     30000      80000\r\n\r\n20 rows selected.\r\n\r\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>If all columns are matched, then no need to use the column name, you can use the Insert into select statement as follows.<\/p>\n<pre>insert into new_table select * from old_table;<\/pre>\n<p>&nbsp;<\/p>\n<p>Let&#8217;s make an real time example as follows.<\/p>\n<pre>SQL&gt; \r\nSQL&gt; create table hr.employees_new as select * from hr.employees where 1=0;\r\n\r\nTable created.\r\n\r\nSQL&gt; select * from hr.employees_new;\r\n\r\nno rows selected\r\n\r\nSQL&gt; \r\nSQL&gt; \r\nSQL&gt; insert into hr.employees_new select * from hr.employees;\r\n\r\n<strong><span style=\"color: #ff0000;\">107 rows created.<\/span><\/strong>\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.\r\n\r\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h5><a href=\"https:\/\/ittutorial.org\/oracle-tutorial-oracle-database-tutorials-for-beginners-junior-oracle-dba\/\"><span style=\"color: #ff0000;\">Do you want to learn Oracle Database for Beginners, then Click and read the following articles.<\/span><\/a><\/h5>\n<p><a href=\"https:\/\/ittutorial.org\/oracle-tutorial-oracle-database-tutorials-for-beginners-junior-oracle-dba\/\" target=\"_blank\" rel=\"noopener noreferrer\">Oracle Database Tutorials for Beginners ( Junior Oracle DBA )<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I will explain Insert Into Select Statement in Oracle SQL in this post of Oracle SQL Tutorial series.<\/p>\n","protected":false},"author":1,"featured_media":16079,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[11435,11439,11437,11440,9037,11434,11430,11438,11441,11442,11436,11443,11351,11304,3001,11302,2673,11301,3715],"class_list":["post-16113","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-how-to-insert-data-into-table-by-using-pl-sql-procedure","tag-how-to-insert-values-into-a-particular-column-in-oracle","tag-insert-into-select-from-multiple-tables-oracle","tag-insert-query-in-oracle","tag-oracle-dba-tutorial","tag-oracle-insert-into-multiple-rows","tag-oracle-insert-into-select","tag-oracle-insert-into-select-from-another-table","tag-oracle-insert-into-select-from-same-table","tag-oracle-insert-into-select-all-columns","tag-oracle-insert-multiple-rows-from-select","tag-oracle-insert-with-select-subquery","tag-oracle-live-sql","tag-oracle-sql-developer-tutorial","tag-oracle-sql-tutorial","tag-oracle-sql-tutorial-pdf","tag-oracle-tutorial","tag-oracle-tutorial-pdf","tag-pl-sql-tutorial"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16113","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=16113"}],"version-history":[{"count":3,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16113\/revisions"}],"predecessor-version":[{"id":32206,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16113\/revisions\/32206"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/16079"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=16113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=16113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=16113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}