Insert Into in Oracle SQL | Oracle SQL Tutorials -7

Hi,

I will explain INSERT INTO Statement in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

ORDER BY in Oracle SQL | Oracle SQL Tutorials -6

INSERT INTO

• The ‘ INSERT INTO ‘ command is used to add new record or data to a table.
• Here is the syntax for adding a data to a table :

INSERT INTO Syntax

There are 2 type of Insert into syntax as follows.

If you insert new record for only specific columns, you should specify their column names as follows.

INSERT INTO table_name (column1, column2, column3, column4...)
VALUES (value1, value2, value3, value4...);

If you know all column order , and insert new records for all columns, you can use the following syntax.

INSERT INTO table_name
VALUES (value1, value2, value3, value4 ...);

You can insert new records into hr.departments as follows. Once you inserted, don’t forget to run Commit command, otherwise this insert operation is not commited and other sessions won’t see this insert.

INSERT INTO hr.departments VALUES (11, 'Computer Engineering',201,1700);

COMMIT;

Run insert as follows.

SQL> INSERT INTO hr.departments VALUES (11, 'Computer Engineering',201,1700);

1 row created.

SQL> COMMIT ;

Commit complete.

SQL>

You can insert only specific columns as follows. Then query the table and review new records.

SQL> INSERT INTO HR.DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME) VALUES(2010,'IT TUTORIAL');

1 row created.

SQL> COMMIT;

Commit complete.



SQL> select * from HR.DEPARTMENTS;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500
           90 Executive                             100        1700
          100 Finance                               108        1700
          110 Accounting                            205        1700

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          120 Treasury                                         1700
          130 Corporate Tax                                    1700
          140 Control And Credit                               1700
          150 Shareholder Services                             1700
          160 Benefits                                         1700
          170 Manufacturing                                    1700
          180 Construction                                     1700
          190 Contracting                                      1700
          200 Operations                                       1700
          210 IT Support                                       1700
          220 NOC                                              1700

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700
          250 Retail Sales                                     1700
          260 Recruiting                                       1700
          270 Payroll                                          1700
           11 Computer Engineering                  201        1700
         2010 IT TUTORIAL

29 rows selected.

SQL>

INSERT DATA FROM ANOTHER TABLE

• use sub-query to copy data from another table.
• The VALUES statement is not used.
• The column count in the subquery and INSERT statement must be same.
• All the records that came from the subquery is inserted to table.
• Let’s add a record to hr.jobs table from hr.departments.

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;

COMMIT;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )