{"id":16107,"date":"2020-05-21T23:30:30","date_gmt":"2020-05-21T23:30:30","guid":{"rendered":"https:\/\/ittutorial.org\/?p=16107"},"modified":"2020-05-21T23:30:30","modified_gmt":"2020-05-21T23:30:30","slug":"insert-into-in-oracle-sql-oracle-sql-tutorials-7","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/insert-into-in-oracle-sql-oracle-sql-tutorials-7\/","title":{"rendered":"Insert Into in Oracle SQL | Oracle SQL Tutorials -7"},"content":{"rendered":"<p align=\"left\">Hi,<\/p>\n<p align=\"left\">I will explain INSERT INTO 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<p align=\"left\">\n<p align=\"left\">\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=\"9FQR0rYkeT\"><p><a href=\"https:\/\/ittutorial.org\/order-by-in-oracle-sql-oracle-sql-tutorials-6\/\">ORDER BY in Oracle SQL | Oracle SQL Tutorials -6<\/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;ORDER BY in Oracle SQL | Oracle SQL Tutorials -6&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/order-by-in-oracle-sql-oracle-sql-tutorials-6\/embed\/#?secret=4PfEbP9Hdl#?secret=9FQR0rYkeT\" data-secret=\"9FQR0rYkeT\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p align=\"left\">\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">INSERT INTO<\/span><\/h4>\n<ul>\n<li>The &#8216; INSERT INTO &#8216; command is used to add new record or data to a table.<\/li>\n<li>Here is the syntax for adding a data to a table :<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10537\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-121.png\" width=\"502\" height=\"81\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-121.png 787w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-121-300x48.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-121-768x124.png 768w\" sizes=\"auto, (max-width: 502px) 100vw, 502px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h5><span style=\"color: #ff0000;\">INSERT INTO Syntax<\/span><\/h5>\n<p>There are 2 type of Insert into syntax as follows.<\/p>\n<p>If you insert new record for only specific columns, you should specify their column names as follows.<\/p>\n<pre><span class=\"sqlkeywordcolor\">INSERT<\/span>\u00a0<span class=\"sqlkeywordcolor\">INTO<\/span>\u00a0<em>table_name<\/em>\u00a0(<em>column1<\/em>,<em>\u00a0column2<\/em>,<em>\u00a0column3<\/em>, <em>column4<\/em>...)\r\n<span class=\"sqlkeywordcolor\">VALUES<\/span>\u00a0(<em>value1<\/em>,<em>\u00a0value2<\/em>,<em>\u00a0value3<\/em>, <em>value4<\/em>...);<\/pre>\n<p>&nbsp;<\/p>\n<p>If you know all column order , and insert new records for all columns, you can use the following syntax.<\/p>\n<pre><span class=\"sqlkeywordcolor\">INSERT<\/span>\u00a0<span class=\"sqlkeywordcolor\">INTO<\/span>\u00a0<em>table_name<\/em>\r\n<span class=\"sqlkeywordcolor\">VALUES<\/span>\u00a0(<em>value1<\/em>,<em>\u00a0value2<\/em>,<em>\u00a0value3<\/em>, <em>value4<\/em> ...);<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>You can insert new records into hr.departments as follows. Once you inserted, don&#8217;t forget to run Commit command, otherwise this insert operation is not commited and other sessions won&#8217;t see this insert.<\/p>\n<p>&nbsp;<\/p>\n<pre>INSERT INTO hr.departments VALUES (11, 'Computer Engineering',201,1700);\r\n\r\nCOMMIT;<\/pre>\n<p>&nbsp;<\/p>\n<p>Run insert as follows.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; INSERT INTO hr.departments VALUES (11, 'Computer Engineering',201,1700);\r\n\r\n1 row created.\r\n\r\nSQL&gt; COMMIT\u00a0;\r\n\r\nCommit complete.\r\n\r\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>You can insert only specific columns as follows. Then query the table and review new records.<\/p>\n<pre>SQL&gt; INSERT INTO HR.DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME) VALUES(2010,'IT TUTORIAL');\r\n\r\n1 row created.\r\n\r\nSQL&gt; COMMIT;\r\n\r\nCommit complete.\r\n\r\n\r\n\r\nSQL&gt; select * from HR.DEPARTMENTS;\r\n\r\nDEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID\r\n------------- ------------------------------ ---------- -----------\r\n           10 Administration                        200        1700\r\n           20 Marketing                             201        1800\r\n           30 Purchasing                            114        1700\r\n           40 Human Resources                       203        2400\r\n           50 Shipping                              121        1500\r\n           60 IT                                    103        1400\r\n           70 Public Relations                      204        2700\r\n           80 Sales                                 145        2500\r\n           90 Executive                             100        1700\r\n          100 Finance                               108        1700\r\n          110 Accounting                            205        1700\r\n\r\nDEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID\r\n------------- ------------------------------ ---------- -----------\r\n          120 Treasury                                         1700\r\n          130 Corporate Tax                                    1700\r\n          140 Control And Credit                               1700\r\n          150 Shareholder Services                             1700\r\n          160 Benefits                                         1700\r\n          170 Manufacturing                                    1700\r\n          180 Construction                                     1700\r\n          190 Contracting                                      1700\r\n          200 Operations                                       1700\r\n          210 IT Support                                       1700\r\n          220 NOC                                              1700\r\n\r\nDEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID\r\n------------- ------------------------------ ---------- -----------\r\n          230 IT Helpdesk                                      1700\r\n          240 Government Sales                                 1700\r\n          250 Retail Sales                                     1700\r\n          260 Recruiting                                       1700\r\n          270 Payroll                                          1700\r\n           11 Computer Engineering                  201        1700\r\n         2010 IT TUTORIAL\r\n\r\n29 rows selected.\r\n\r\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/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<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<h4><span style=\"color: #ff0000;\">Do you want to learn Oracle Database for Beginners, then read the following articles.<\/span><\/h4>\n<blockquote class=\"wp-embedded-content\" data-secret=\"BKw3I6CTWt\"><p><a href=\"https:\/\/ittutorial.org\/oracle-tutorial-oracle-database-tutorials-for-beginners-junior-oracle-dba\/\">Oracle Tutorial | Oracle Database Tutorials for Beginners  ( Junior Oracle DBA )<\/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;Oracle Tutorial | Oracle Database Tutorials for Beginners  ( Junior Oracle DBA )&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/oracle-tutorial-oracle-database-tutorials-for-beginners-junior-oracle-dba\/embed\/#?secret=eRP6m7OHEz#?secret=BKw3I6CTWt\" data-secret=\"BKw3I6CTWt\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, I will explain INSERT INTO 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,11433,11439,11426,11429,11437,11428,11440,11431,11434,11430,11438,11436,11351,11424,11425,11302,11301,3715,11432,11427,2605],"class_list":["post-16107","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-in-particular-column-in-sql","tag-how-to-insert-values-into-a-particular-column-in-oracle","tag-insert-into-multiple-rows","tag-insert-into-oracle","tag-insert-into-select-from-multiple-tables-oracle","tag-insert-into-table-from-another-table-oracle","tag-insert-query-in-oracle","tag-oracle-insert-into","tag-oracle-insert-into-multiple-rows","tag-oracle-insert-into-select","tag-oracle-insert-into-select-from-another-table","tag-oracle-insert-multiple-rows-from-select","tag-oracle-live-sql","tag-oracle-live-sql-tutorial","tag-oracle-sql-tutorial-oracle-sql-developer","tag-oracle-sql-tutorial-pdf","tag-oracle-tutorial-pdf","tag-pl-sql-tutorial","tag-sql-insert-date","tag-sql-insert-into-select","tag-what-is-oracle"],"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\/16107","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=16107"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16107\/revisions"}],"predecessor-version":[{"id":16111,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16107\/revisions\/16111"}],"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=16107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=16107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=16107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}