Example Using the INSERT Statement

This section consists of an example using the INSERT statement.

• Using the INSERT Statement

Using the INSERT Statement

TimesTen supports the TimesTen DML statements INSERT, UPDATE, DELETE, and MERGE.

This example uses the AS SELECT query clause to create table emp_copy, sets AUTOCOMMIT off, creates a sequence to increment employee_id, and uses the INSERT statement in PL/SQL to insert a row of data in table emp_copy.

Command> CREATE TABLE emp_copy AS SELECT * FROM employees;
107 rows inserted.
Command> SET AUTOCOMMIT OFF;

Command> CREATE SEQUENCE emp_copy_seq
         START WITH 207
         INCREMENT BY 1;

Command>  BEGIN
            INSERT INTO emp_copy
              (employee_id, first_name, last_name, email, hire_date, job_id,
               salary)
            VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
              'AD_ASST', 4000);
          END;
          /
 
PL/SQL procedure successfully completed.

Continuing, the example confirms the row was inserted, then rolls back the transaction.

Command> SELECT * FROM EMP_COPY WHERE first_name = 'Parker';
< 207, Parker, Cores, PCORES, <NULL>, 2008-07-19 21:49:55, AD_ASST, 4000, 
<NULL>, <NULL>, <NULL> >
1 row found.
Command> ROLLBACK;
Command>  SELECT * FROM emp_copy WHERE first_name = 'Parker';
0 rows found.

Now INSERT is executed again, then the transaction is rolled back in PL/SQL. Finally, the example verifies that TimesTen did not insert the row.

Command> BEGIN
           INSERT INTO emp_copy
             (employee_id, first_name, last_name, email, hire_date, job_id,
              salary)
           VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
                   'AD_ASST',4000);
         ROLLBACK;
         END;
         /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM emp_copy WHERE first_name = 'Parker';
0 rows found.