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.

 

 

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.

 99 views today,  1 views in last 3 hour

Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply