Oracle_Insert into

into-db2

admin DML Statements, Oracle , ,

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::=

INSERT
INTO table_name {(col1, col2…colN)} VALUES (‘value1’,
‘value2’ … ‘valueN’ );

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:

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.

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.

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

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.

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

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!

You May Also Like..

NULLIF

Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]

WHERE

SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]

comments

Comment in SQL and PL-SQL

Hi DBgeek Army, Welcome back to the world of Oracle! Comments are very important aspects of our code. No matter […]

Leave a Reply

Your email address will not be published. Required fields are marked *