SQL INSERT INTO Statement

The INSERT statement is used to add new row to a table. To insert new row(s) into a table, the table must be in your own schema or you must have INSERT privilege on the table. Only one row is inserted at a time with this syntax. You can insert literal values (26.5, ‘DINESH), expres­sions containing operators and functions, null values etc. only.

Here <tablename> corresponds to the name of table in which we will insert the data. The <columnnamel>, <columnname2>] and so on are optional and corresponds to the name of the columns in which values will be entered. The <columnvaluel> corresponds to the value to be entered in <columnnamel> and similarly <columnvalue2> corresponds to <columnname2> and so on. As you can insert a new row that contains values for each column, the column list is optional. If you do not specify the column list then the values corresponding to each row must be listed according to the default order of the columns in the table.

The Syntax is
 
INSERT INTO <table_name> 
[( <columnname1>[,<columnvalue2> ] .....[<columnnameN>])] 
VALUES(<columnvalue1>[,<columnvalue2> ] ..... [,<columnvalueN>]); 

In order to insert data, let us first create an INSTRUCTOR table using CREATE TABLE command.

SAL> CREATE TABLE INSTRUCT (INST_ID NUMBER(4) PRIMARY KEY , NAME VARCHAR2(15) NOT NULL, DOB DATE NOT NULL, POST VARCHAR2(20), PHONE NUMBER (8)); 
Table Created.
 
Now we will insert data into the INSTRUCTOR table
 
 SQL> INSERT INTO INSTRUCTOR (INST_ID, NAME, DOB, POST, PHONE) VALUES (100, 'DINESH', '15-AUG-78', 'LECTURER', 28378); 
1 row created. 

On execution of the above statement, a message’ 1 row created’ will be prompted if there are no errors in the INSERT statement. This will insert the above row into the INSTRUCTOR table. The user must take care that the column’s data type and its corresponding values must match. Similarly, you can add other rows by executing the INSERT statements with different set of values.

SQL> INSERT INTO INSTRUCTOR VALUES (101, 'PUNAR', '10-JAN-78', 'LECTURER', 235647); 
1 row created. 

The above statement on execution will also insert a row into the INSTRUCTOR table.

If you want to see the contents of the INSTRUCTOR table after executing the above statements, use the SELECT statement.

SQL> SELECT * From INSTRUCTOR; 

This statement will retrieve all the rows from the INSTRUCTOR table.

You can insert rows into the table without specifying the columnnames because they are optional. Although it is always recommended that the column names must be specified when in­serting rows into a table, because it is possible that the number of columns might increase or decrease which results in failure of INSERT statement. Wrong values for the column names may be entered even if INSERT statement is successful.

For example – In the name column, you have entered ‘READER’ and in post column you have entered ‘DINESH’.

The following points should be considered while inserting the data into the tables.

• If you do not specify the column name when inserting a row, the Oracle uses all the columns by default. In addition, the column’s order that Oracle uses is the order in which the columns were specified when the table is created which you can see using SQL *PLUS Describe command.
• The number of columns in the list of column names must match the number of values that appear in parentheses after the keyword ‘VALUES’.
• The data types for a column and its corresponding value must match. For example: If the data type of a column is ‘Number’ then you cannot enter ‘Abc’ i.e. string values into it.  The newly inserted row goes into a table at an arbitrary location i.e the table has no implied ordering.
• The character data will be enclosed within single quotes. In other words, columns having  character datatype will allow only character data enclosed in single quotes. If you want to display quotes then use two quotes for everyone to be displayed. E.g

SQL> Select 'A "quoted" word' AS text FROM DUAL; 

Whenever you insert values for a Date data type column, then it must be specified in single quotes(‘). Oracle automatically converts the character field to date data type field. The date is usually entered in DD-MON- YY format. If a date is to be entered in a format other than the default format, you must use the TO_DATE function to convert it into required format.

If the user wants to enter a NULL value for a particular column, then it can be done by specifying NULL keyword in the VALUES clause.

SQL> INSERT INTO INSTRUCTOR VALUES (102, 'VIVEK', '29-FEB-76', 'READER', NULL); 
1 Row Created 

You can also insert the expression containing operators and functions, pseudo columns (such as sysdate, user) etc.

SQL> INSERT INTO INSTRUCTOR VALUES (100+3, 'MEGHA', SYSDATE, 'PROFESSOR', 2700901);
1 row created. 

If you have specified DEFAULT value in column specification while creating a table, then you mayor may not need to specify value for that column in INSERT statement.

SQL> INSERT INTO INSTRUCTOR VALUES (DEFAULT, 'AARUSHI', SYSDATE, 'READER', NULL); 
1 row created. 

Here, the INSERT statement uses the default value for INST _ID column. If there is no default value defined for the column, a null value is inserted instead.

INSERT WITH SELECT

You can also insert values that are selected from one table into another table using a SELECT statement.

The Syntax is: 
INSERT INTO <tablename> 
[( <columnnamel> [,<columnvalue2> ] ..... [,<columnvalueN>])] SELECT_statement; 

Here the columnname (1 … n) are optional but if you specify it then the number of columns referenced in the INSERT statement must equal to number of columns in the SELECT statement.
Consider a table TECH_INFO already created and having the same column’s datatype and constraints as that of INSTRUCTOR table. So to insert all the rows existing in the IN­STRUCTOR table into the TECH_INFO table, the statement is

SQL> INSERT INTO TECH_INFO SELECT * FROM INSTRUCTOR; 
4 ROW CREATED 

This statement will insert all the records of the INSTRUCTOR table into that of TECH_INFO.

Consider a table TECH_REPORT already created and having columns TECH_ID (Num­ber(4) and primary key), dob (date) and NAME (varchar2(15)). So to insert all the rows having POST=’READER’ from INSTRUCTOR table into the TECH_REPORT table, execute the state­ment

SQL> INSERT INTO TECH_REPORT ( TECH_ID , DOB , NAME) SELECT INST_ID, DOB, NAME FROM INSTRUCTOR WHERE POST = 'READER'; 

This statement will insert all rows of the Instructor’s table having POST of a Reader.

INSERTING ROWS USING SUBSTITUTION VARIABLE (&)

A substitution variable is a user variable name proceeded by one or two ampersands. The substitution variable with single ampersand is known as a temporary substitution variable and the substitution variable with double ampersand is known as a permanent substitution variable. The SQL *PLUS will prompt for a value if an undefined ampersand or double ampersand variable is found. The double ampersand will define the variable and thereby preserve its value. But single ampersand will not preserve the value but only substitute what is entered at one time. We will discuss them in detail in our later section.

Inserting rows using substitution variables help us to insert rows into a table more effi­ciently and in a faster manner in comparison to the ordinary method. Now let us insert rows into INSTRUCTOR table using a substitution variable.

SQL> INSERT INTO INSTRUCTOR (INST_ID, NAME, DOB, POST, PHONE) VALUES (&A, '&B', '&C', '&D', &E); 

On execution, it will prompt you to enter the values Similarly, you can insert more records one by one either by using the INSERT statements again and again or by using / or RUN command at the SQL prompt which executes the last executed statement.

On inserting the four records into the INSTRUCTOR table and executing SELECT command to retrieve the records, the table will look like.

SQL> SELECT * FROM INSTRUCTOR; 

The following points should be kept in mind while using INSERT with substitution variables.

• If you don’t specify the single quotes for the character and date data types in the INSERT statement then the user must include the quotes when prompted for the value.
• To avoid the display of old and new values during the execution of INSERT statement with substitution variables use the SET VERIFY OFF statement at the SQL prompt.
• If you specify the value that doesn’t meet the requirements of a particular column then you have to reenter all the values for the substitution variables.