DB GEEK

Hello guys,
In one of our previous blogs, we have learned how to create a table in Oracle database.
Do read if you haven’t read Create Table blog. In this blog, I will introduce a few ways to insert rows into an existing table using INSERT INTO statement.
Yes, there are multiple ways to insert rows into the table using INSERT INTO statement.
Let’s discuss what those are.

Before that let’s have a look at the syntax.

Syntax::=

This is a basic syntax of INSERT INTO statement. We will keep modifying it in the upcoming examples.
Table_name – table name in which the data is to be inserted.
col1, col2…colN – These are the column names of the table. If we omit these column names, we will have to provide values for all the columns available in the table.
value1 – value that has to be inserted into col1 of the table.

In this blog, we will use the same table which we created while learning CREATE TABLE statement. To insert new data, I have deleted the old data from the table. I will explain how to delete data from the table in upcoming blogs.

Following are the ways to use INSERT INTO statement.

1. When we have some hardcoded (static values) to insert into the table-
It can be further divided into 2 sub-categories.

a. Inserting the data into all the columns.
In this case, we will use the same syntax but we will not need to mention columns of the EMP table but need to provide values for all the columns available in the table. Otherwise it will cause an error.

Example:

INSERT INTO emp VALUES (
    'PAUL',
    'SMITH',
    'PAUL.SMITH'
);


Output…
1 row inserted.

In the above snippet, you can see that we have inserted a row using INSERT INTO statement. Also, we provided values to all the columns.
You can check values in the emp table by firing the SELECT statement.

SELECT
    *
FROM
    emp;


Output…

FIRST_NAME           LAST_NAME                 EMAIL                    
-------------------- ------------------------- -------------------------
PAUL                 SMITH                     PAUL.SMITH               

You can see that now the table has one row with the values we provided in the INSERT INTO statement.

b. Inserting the data into selected columns-
In this case, we will need to specify column names of the emp table in the INSERT INTO statement.
Let’s go through the below example.

INSERT INTO emp (
    first_name,
    email
) VALUES (
    'JENNIFER',
    'JENN.NITHALI'
);

Output…
1 row inserted.

So using the above INSERT INTO statement, we have inserted one more row into the emp table.
Let’s check the table now.

SELECT
    *
FROM
    emp;


Output…


FIRST_NAME           LAST_NAME                 EMAIL                    
-------------------- ------------------------- -------------------------
PAUL                 SMITH                     PAUL.SMITH               
JENNIFER                                       JENN.NITHALI             

You can see the output that we do not have any value in the LAST_NAME column for the 2nd row as we didn’t provide any values in the 2nd INSERT INTO statement to the LAST_NAME column.

2. Inserting data from one table to another table-
If you need to insert any selected data from one table to another table then you will need to write your insert statement as follows.

Insert
    INTO tab1 (
        col1,
        col2,
        col3…coln
    )
( SELECT
    col1,
    col2,
    col3…coln
  FROM
    tab2
WHERE condition);

Let’s do insert some data from Employees table of HR schema into our existing table emp.

INSERT INTO emp (
    first_name,
    last_name,
    email
)
    ( SELECT
        first_name,
        last_name,
        email
      FROM
        hr.employees
      WHERE
        employee_id BETWEEN 100 AND 105
    );

	
Output…
6 rows inserted.

In the above code we can omit WHERE clause but in this case, all the data selected with select statement will get inserted into emp table. Also, a sequence of the columns in the select statement should be same as columns mentioned in the Insert statement after table name in the parenthesis.
There are a couple of more ways to insert data into the Oracle Database.

If you have any questions please comment below or feel free to write me an email.

If you like this blog, do share with your friends and colleagues on social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!