Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Tuesday, 11 April 2017

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


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

   
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
  

Share this article :

0 comments:

Post a Comment