SQL Table Insert

This will be used to insert the records into table.We have two methods to insert.

• Insert Values
• Insert values by using address

INSERT VALUES

Add new rows to a table by using the INSERT statement.

Syntax:         

 

     insert into <table_name) values (value1, value2, value3 …. Valuen);

You can add new rows to a table by issuing the INSERT statement.

In the syntax:

          table                     is the name of the table

          column                 is the name of the column in the table to populate

          value                     is the corresponding value for the column

Note: This statement with the VALUES clause adds only one row at a time to a table.

 

Example:

            SQL> INSERT INTO student VALUES (1, ’sudha’, 100);

            SQL> INSERT INTO student VALUES (2, ’saketh’, 200);

         

To insert a new record again you have to type entire insert command, if there are lot  of  records this will be difficult.

This will be avoided by using address method.

INSERT VALUES BY USING ADDRESS

   
Syntax:

          insert into <table_name) values (&col1, &col2, &col3 …. &coln);

     This will prompt you for the values but for every insert you have to use forward        slash.

   
Example:

            SQL>  INSERT INTO student VALUES (&no, '&name', &marks);

Enter value for no: 1

Enter value for name: Jagan

Enter value for marks: 300

old   1: insert into student values(&no, '&name', &marks)

new   1: insert into student values(1, 'Jagan', 300)

SQL> /

Enter value for no: 2

Enter value for name: Naren

Enter value for marks: 400

old   1: insert into student values(&no, '&name', &marks)
new   1: insert into student values(2, 'Naren', 400)

INSERT VALUES INTO SPECIFIED COLUMNS

   Syntax:

                insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 ….Valuen);

Example:

            SQL> INSERT INTO student (no, name) VALUES (3, ’Ramesh’);

            SQL> INSERT INTO student (no, name) VALUES (4, ’Madhu’);

INSERT VALUES INTO SPECIFIED COLUMNS BY USING ADDRESS

Syntax:

          insert into <table_name)(col1, col2, col3 … coln) values (&col1, &col2, &col3 …. &coln);

     This will prompt you for the values but for every insert you have to use forward
slash.

Example:

            SQL> INSERT INTO student (no, name) VALUES (&no, '&name');

Enter value for no: 5

Enter value for name: Visu

old   1:  insert into student (no, name) values(&no, '&name')

new   1:  insert into student (no, name) values(5, 'Visu')

SQL> /

Enter value for no: 6

Enter value for name: Rattu

old   1:  insert into student (no, name) values(&no, '&name')
new   1:  insert into student (no, name) values(6, 'Rattu')

INSERT VALUES INTO TABLE BY USING SELECT STATEMENT

   Syntax:

     INSERT INTO <Table_name1> SELECT * FROM <Table_name2>

                            

   Example:

     INSERT INTO Emp_bk SELECT * FROM emp;

INSERT VALUES INTO TABLE BY USING SELECT STATEMENT INTO SPECIFIED COLUMNS

  
   Syntax:

   

   INSERT INTO <Table_name1> (Column1, Column2, Column3, Column3)            SELECT Column1, Column2, Column3, Column4 from <Table_name2>

                   

  Example:

  INSERT INTO Emp_bk (EMPNO,ENAME,SAL,DEPTNO)  select 
    EMPNO,ENAME,SAL,DEPTNO  from emp