Insert

INSERT:

Syntax:

Insert into <tablename> [collist] Values( valueList);

Note:   Strings and Dates must be enclosed in ‘’.

Ex:       Consider table Emp with (empno, ename, sal, deptno)

1.    INSERT INTO emp VALUES (101, ‘JOHN’, 10000, 10);

2.    INSERT INTO emp (empno, ename,)  VALUES (102, ‘JAMES’);

Q) How to Insert null  values.

- Null means Empty. It is not equal to 0, ‘’.Its nothing.

- Null values are inserted when the date for the respective column is not provided or is empty.

- Null values can be inserted implicitly by Oralce(system) or explicitly by User.

- Explicitly:

            If it is a numeric column, specify NULL

            If it is a String/ Date column, specify ‘’

INSERT into EMP values (101,’abc’,null,null);

Q) How to insert multiple records using single insert statement.?

- We can use substitution variables.

- These variables must be prefixed with &, &&

INSERT into EMP values(&eno,’&ename’,&sal,&deptno);

Steps:

1.    Construct a query similar to the above query.

a.     Numeric – just prefix

b.    String – prefix along with single codes.

2.    Execute the query in the SQL PLUS. It will ask to enter values, fill it up.

3.    To re-execute it, use ‘/’

4.    If you want to repeat the same value for many columns, use &&.

Q3) How to copy data from one table to another table ?

Syntax:

            INSERT into <targetTableName>

            SELECT <collist/*> from <sourceTableName>

Ex:

            (insert all columns)

            INSERT into EMPT

            SELECT * from EMPS;

            (insert some columns, but EMPT must contain only empno,ename,sal)

            INSERT into EMPT

            SELECT empno,ename,sal from EMPS;