8. OpenSQL Statements : INSERT : INSERT Examples
 
Share this page                  
INSERT Examples
1. Add a row to an existing table.
INSERT INTO emp (name, sal, bdate)
    VALUES ('Jones, Bill', 10000, 1944);
2. Insert into the job table all rows from the newjob table where the job title is not Janitor.
INSERT INTO job (jid, jtitle, lowsal, highsal).
SELECT job_no, title, lowsal, highsal
FROM newjob
WHERE title <> 'Janitor';
3. Add a row to an existing table, using the default columns.
INSERT INTO emp
    VALUES ('Jones, Bill', 10000, 1944);
4. Use a structure to insert a row.
/* Description of table employees from
    database deptdb */

EXEC SQL DECLARE employees TABLE
   (eno           SMALLINT NOT NULL,
    ename         CHAR(20) NOT NULL,
    age           SMALLINT,
    jobcode       SMALLINT,
    sal           FLOAT NOT NULL,
    deptno        SMALLINT);

EXEC SQL BEGIN DECLARE SECTION;

    emprec
        int           eno;
        char          ename[21];
        int           age;
        int           job;
        float         sal;
        int           deptno;

EXEC SQL END DECLARE SECTION;
  /* Assign values to fields in structure */
 
  eno = 99;
  ename = "Arnold K. Arol”;
  age = 42;
  jobcode = 100;
  sal = 100000;
  deptno=47;
 
  EXEC SQL CONNECT deptdb;
  EXEC SQL INSERT INTO employees VALUES (:emprec);
  EXEC SQL DISCONNECT;
5. Insert explicit values into a t1 row regardless of the identity column definition. Without the OVERRIDING clause, this statement generates a syntax error.
INSERT INTO t1 OVERRIDING SYSTEM VALUE VALUES(1, 2, 3);
6. Insert four rows into table emp.
INSERT INTO emp (empno, deptno, sal)
    VALUES (698, 300, 850), (840, 100, 4400), (789, 400, 6500), (299, 500, 3900);