Inserting rows using the VALUES clause

An example of this is to insert a new row into the DEPARTMENT table. The columns for the new row are as follows:

• Department number (DEPTNO) is 'E31'
• Department name (DEPTNAME) is 'ARCHITECTURE'
• Manager number (MGRNO) is '00390'
• Reports to (ADMRDEPT) department 'E01'

The INSERT statement for this new row is as follows:

INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT)
   VALUES('E31', 'ARCHITECTURE', '00390', 'E01');

This statement can alternately be written using the named columns syntax. This form of insert lists each column name directly with the corresponding value.

INSERT INTO DEPARTMENT 
   VALUES(DEPTNO=>'E31', DEPTNAME=>'ARCHITECTURE', MGRNO=>'00390', ADMRDEPT=>'E01');

You can also insert multiple rows into a table using the VALUES clause. The following example inserts two rows into the PROJECT table. Values for the Project number (PROJNO) , Project name (PROJNAME), Department number (DEPTNO), and Responsible employee (RESPEMP) are given in the values list. The value for the Project start date (PRSTDATE) uses the current date. The rest of the columns in the table that are not listed in the column list are assigned their default value.

INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE)
   VALUES('HG0023', 'NEW NETWORK', 'E11', '200280', CURRENT DATE),
         ('HG0024', 'NETWORK PGM', 'E11', '200310', CURRENT DATE);

Using the named columns syntax, the column names must be included for every row. The INSERT statement can be written like this:

INSERT INTO PROJECT 
   VALUES(PROJNO=>'HG0023', PROJNAME=>'NEW NETWORK', DEPTNO=>'E11', RESPEMP=>'200280', PRSTDATE=>CURRENT DATE),
         (PROJNO=>'HG0024', PROJNAME=>'NETWORK PGM', DEPTNO=>'E11', RESPEMP=>'200310', PRSTDATE=>CURRENT DATE);