Wednesday , November 13 2019
Home / SQL / INSERT statement for single row and multiple rows

INSERT statement for single row and multiple rows

INSERT statement for single row:

The INSERT statement is used to insert new data into the oracle table. It can insert single or multiple new rows in the table of oracle database.

Syntax

The Syntax for simple INSERT statement:

INSERT INTO table_name VALUES ( value_1, value_2, value_3, value_4 );

Here,

INSERT INTO   keyword is used to insert data.

table_name   is the name of the table where data will be stored.

VALUES   keyword is used to contain values that will be stored into the table and the values will be contained within Starting and Ending parenthesis.

(value_1, value_2, value_3, value_4) These are values those will be stored into the table. Every value is separated by comma ( , )  and no comma is required after last value.

Example:

INSERT INTO Employee VALUES (‘EMP-001′,’MD. SHARIFUL ALAM’,’MYMENSINGH’,’10-MAY-2015′);

In the example, the values (‘EMP-001′,’MD. SHARIFUL ALAM’,’MYMENSINGH’,’10-MAY-2015′) are storing the EMPLOYEE table.

Note: The character, string, date values will be within single quotation (‘  ‘) and no quotation is required for numeric data. In last, semi colon (;) will be used to terminate the INSERT statement.

Note: In case of simple INSERT statement, values should be provided serially as per the table column serial. The value of first column should be provided first, secondly for 2nd column, thirdly for 3rd column, fourthly for 4th column and  so on.

Suppose, you have created a table that contained the columns (Employee_ID, Employee_Name, Address, Join_Date) in serial. When you provide values in simple INSERT statement then first you have to provide Employee_ID, then Employee_Name, Address, and at last  Join_Date like ‘EMP-001′,’MD. SHARIFUL ALAM’,’MYMENSINGH’,’10-MAY-2015′ respectively as per the column serial of database table.

The Syntax of INSERT statement for partial value:

INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, value_2, value_3);

Here, (column_1, column_2, column_3) These are the columns where values will be stored. In this syntax, the values will be provided as per the column serial mentioned before the VALUES keyword. The column name will exist within the Staring an Ending parenthesis.

Suppose, you have created a table containing four columns named Employee_ID, Employee_Name, Address, Join_Date in serial. But you have mentioned the three columns named Employee_Name, Employee_ID, Join_Date in serial before the VALUES keyword. When you will provide after the VALUES keyword, you have to follow the column serial that have been mentioned before VALUES keyword. So, you have to provide first the value of column Employee_Name, then Employee_ID and lastly Join_Date.

Note: You can provide partial number of column values instead of the number of columns in table. For example, you have created table by four columns, but you can provide values for three columns.

Example:

INSERT INTO Employee (Employee_Name, Employee_ID, Join_Date) VALUES (‘MD. SHARIFUL ALAM’, ‘EMP-001′, ’10-DEC-2015’);

In this example, the column names have been mentioned before the VALUES keyword and the values of these columns have been provided as per column serial mentioned before the VALUES keyword.

INSERT statement for multiple  rows:

The INSERT ALL statement is used to insert multiple rows.

Syntax

INSERT ALL

INTO    table_name     (column_1_of_the_first_row, column_2_of_the_first_row, column_3_of_the_first_row) VALUES   (value_1_of_the_first_row, value_2_of_the_first_row, value_3_of_the_first_row)

INTO    table_name   (column_1_of_the_second_row, column_2_of_the_second_row, column_3_of_the_second_row)  VALUES   (value_1_of_the_second_row, value_2_of_the_second_row, value_3_of_the_second_row)

INTO    table_name    (column_1_of_the_third_row, column_2_of_the_third_row, column_3_of_the_third_row)  VALUES    (value_1_of_the_third_row, value_2_of_the_third_row, value_3_of_the_third_row)

SELECT * FROM DUAL;

Here,

INSERT ALL statement will be used to insert multiple rows by a single INSERT statement.

INTO table_name will be used to specify the table where data will be inserted. This table name can be same or different.

In last, SELECT * FROM DUAL; this statement should be specify.

Example to insert multiple rows into same table:

We can inert multiple rows into same table by a single INSERT statement.

INSERT ALL

INTO Employee (Employee_ID, Employee_Name, Join_Date) VALUES (‘EMP-001′,’MD. SHARIFUL ALAM’,’10-DEC-2015′)

INTO Employee (Employee_ID, Employee_Name, Join_Date) VALUES (‘EMP-002′,’MD. ABDUR RAHIM’,’21-JUL-2014′)

INTO Employee (Employee_ID, Employee_Name, Join_Date) VALUES  (‘EMP-003′,’MD. ABDUR RASHID’,’12-JUN-2015′)

SELECT * FROM DUAL;

In the example, three rows have been inserted into the same table named EMPLOYEE by a single INSERT statement.

Example to insert multiple rows into different tables:

We can insert multiple rows into different tables by a single INSERT statement. To perform this practice, follow the steps below:

i) First create two tables named Employee and Salary.

CREATE TABLE   Employee

(

Employee_Id                 varchar2(10),

Employee_Name           varchar2(100),

Address                            varchar2(200),

Join_Date                        date

);

CREATE TABLE   Salary

(

Employee_Id                 varchar2(10),

Salary_Amount             number(10,2),

Payment_Date               date

);

(ii) Now execute the INSERT statement below-

INSERT ALL

INTO Employee (Employee_ID, Employee_Name, Join_Date) VALUES (‘EMP-001′,’MD. SHARIFUL ALAM’,’10-JAN-2015′)

INTO Employee (Employee_ID, Employee_Name, Join_Date) VALUES (‘EMP-002′,’MD. ABDUR RAHIM’,’10-MAY-2015′)

INTO Salary (Employee_Id,Salary_Amount,Payment_Date) VALUES (‘EMP-001′,20000,’30-DEC-2015’)

SELECT * FROM DUAL;

In the example, three rows have been inserted into different table named EMPLOYEE and SALARY by a single INSERT statement. Here first two rows have been inserted into EMPLOYEE table and last one row has been inserted into different table SALARY.

Note:

This example is equivalent to the INSERT statement that will be executed by different INSERT statements. Each statement should be terminated by semi colon (;). But when you insert multiple rows by a single INSERT statement, no semi colon (;) is required for each. Semi colon (;) will be used in last after SELECT * FROM DUAL statement to terminate the entire INSERT statement.

INTO Employee (Employee_ID, Employee_Name, Join_Date) VALUES (‘EMP-001′,’MD. SHARIFUL ALAM’,’10-JAN-2015′);

INTO Employee (Employee_ID, Employee_Name, Join_Date) VALUES (‘EMP-002′,’MD. ABDUR RAHIM’,’10-MAY-2015′);

INTO Salary (Employee_Id,Salary_Amount,Payment_Date) VALUES (‘EMP-001′,20000,’30-DEC-2015’);

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

Leave a Reply

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