The DB2 INSERT statement

INSERT

Inserting into a view inserts into the underlying table unless an INSTEAD OF INSERT trigger handles it. You need INSERT privilege on the target. Check constraints, unique indexes, referential integrity, and BEFORE/AFTER INSERT triggers all run. A failed constraint rolls back that insert (and, for ATOMIC multi-row, the whole statement).

If you omit the column list, the implicit list is all columns of the table (except implicitly hidden columns) in CREATE TABLE order. Beginners should always list columns so the statement survives ALTER TABLE ADD COLUMN.

INSERT VALUES

1
2
3
INSERT INTO DSN8C10.EMP
       (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, HIREDATE, JOB, EDLEVEL, SEX, SALARY)
VALUES ('200340', 'MARY', 'T', 'SMITH', 'D11', CURRENT DATE, 'ANALYST', 18, 'F', 45000.00);

The number of values must equal the number of names in the column list (plus INCLUDE columns when INSERT is nested in SELECT). Multiple values require parentheses. Each item may be an expression, NULL, DEFAULT, or a host variable / host-variable array.

DEFAULT and NULL

1
2
3
4
5
INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
VALUES ('Mary Smith', 35000.00, 11, DEFAULT);
 
INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL, HIRETYPE)
VALUES ('Pat Lee', NULL, 11, 'Associate', DEFAULT);

NULL requires a nullable column. DEFAULT uses the column default (literal, CURRENT DATE, generated identity, and so on). “INSERT default values” on z/OS means: omit columns or write DEFAULT—not a separate DEFAULT VALUES clause like some other products.

INSERT SELECT

The fullselect’s degree must match the insert column list. You may use WITH (CTEs) before the fullselect. Isolation and QUERYNO can appear on the INSERT.

1
2
3
4
INSERT INTO DSN8C10.EMP_PHOTO_RESUME (EMPNO)
SELECT EMPNO
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00';

This is also “INSERT with a subquery”: the source is a query, which may contain its own WHERE, JOINs, and nested subqueries. The INSERT fullselect must not correlate to columns outside itself.

1
2
3
4
5
6
7
INSERT INTO ARCHIVE_EMP (EMPNO, LASTNAME, SALARY)
SELECT E.EMPNO, E.LASTNAME, E.SALARY
  FROM DSN8C10.EMP E
 WHERE E.HIREDATE < DATE('2000-01-01')
   AND NOT EXISTS (
         SELECT 1 FROM ARCHIVE_EMP A WHERE A.EMPNO = E.EMPNO
       );

INSERT with host variables

1
2
3
4
5
6
           EXEC SQL
                INSERT INTO DSN8C10.DEPT
                       (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT)
                VALUES (:HV-DEPTNO, :HV-DEPTNAME, :HV-MGRNO:HV-MGRNO-IND,
                       :HV-ADMRDEPT)
           END-EXEC.

Pair nullable columns with indicator variables. Extended indicators (when enabled) let a special indicator mean DEFAULT or UNASSIGNED (treat like default / skip). If every column is UNASSIGNED or DEFAULT, an insert trigger still fires—IBM documents that trigger activation is not skipped.

Identity and generated columns

1
2
3
4
5
6
7
8
INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
VALUES ('Mary Smith', 35000.00, 11, 'Associate');
-- EMPNO identity, HIRETYPE and HIREDATE defaults are generated
 
-- Copy into a table whose ROWID is also GENERATED ALWAYS
INSERT INTO B.EMP_PHOTO_RESUME
OVERRIDING USER VALUE
SELECT * FROM DSN8C10.EMP_PHOTO_RESUME;

OVERRIDING USER VALUE tells Db2 to ignore user-supplied values for GENERATED ALWAYS or GENERATED BY DEFAULT identity, ROWID, or row-change-timestamp columns and generate them. To retrieve the generated key, use SELECT FROM FINAL TABLE (INSERT …) rather than guessing MAX(EMPNO).

Other generated columns (GENERATED ALWAYS AS expression) are computed; you typically omit them. Do not insert into implicitly hidden columns unless you mean to.

INSERT into temporal tables

System-period temporal tables: row-begin, row-end, and transaction-start-ID are generated. INSERT a business row; Db2 sets SYSTEM_TIME. History rows are written on later UPDATE/DELETE, not on INSERT of a new current row.

Application-period (BUSINESS_TIME) tables: you supply the begin and end of the business period (inclusive-exclusive semantics as defined on the table). Overlapping periods for the same key are rejected. Bitemporal tables combine both rules: you supply BUSINESS_TIME values; SYSTEM_TIME remains generated.

1
2
INSERT INTO POLICY (PK, CUSTOMER, BUS_START, BUS_END, PREMIUM)
VALUES (1, 'A100', DATE('2020-01-01'), DATE('9999-12-31'), 1200.00);

INSERT into XML

XML columns store XML values. Typical sources: XMLPARSE(DOCUMENT :hv), an XML host variable, or XMLDOCUMENT(…). The hidden DOCID column is GENERATED ALWAYS; if you list it, Db2 ignores the supplied value. Validate against an XML schema only if the column (or a type modifier) requires it. Keep documents well-formed; XMLPARSE with STRIP WHITESPACE / PRESERVE WHITESPACE follows IBM XMLPARSE rules.

1
2
INSERT INTO INVOICE (INVNO, INV_DOC)
VALUES (1001, XMLPARSE(DOCUMENT :HV-XML-TEXT));

INSERT into LOBs

CLOB, BLOB, and DBCLOB values come from host variables, LOB locators, or file reference variables (CBL/PLI). The value must fit the LOB column’s length and the LOB tablespace. For large objects, prefer locators so the program does not materialize the whole value in working storage. INSERT still counts as a data-change statement for logging and locking; huge LOBs can dominate unit-of-work time—commit strategy matters.

INSERT with FOR n ROWS

Multiple-row INSERT (Db2 V8+) uses host-variable arrays: one array per column, first element = first row. FOR :n ROWS or FOR integer ROWS sets the count (must not exceed array dimension).

1
2
3
4
INSERT INTO DSN8C10.ACT (ACTNO, ACTKWD, ACTDESC)
VALUES (:HVA1, :HVA2, :HVA3)
FOR :NUM-ROWS ROWS
NOT ATOMIC CONTINUE ON SQLEXCEPTION;

• ATOMIC (default) — if any row fails, undo all rows of this INSERT
• NOT ATOMIC CONTINUE ON SQLEXCEPTION — keep successful rows; try the rest; each successful row is still atomic with its triggers. Static SQL only for this clause; dynamic uses PREPARE attributes

After NOT ATOMIC, use GET DIAGNOSTICS to see which array elements failed. Db2 13 function level 506 adds a programmer-friendly multi-row VALUES list (several parenthesized rows) when APPLCOMPAT is at least V13R1M506—no arrays required for small literal batches.

1
2
3
4
5
-- Requires Db2 13 FL506 / matching APPLCOMPAT
INSERT INTO EMPLOYEE (EMPNO, FIRSTNME, LASTNAME, WORKDEPT)
VALUES ('000206', 'ELIZABETH', 'GRACE', 'A11'),
       ('000207', 'JACK', 'JOHNSON', 'B13'),
       ('000208', 'JENNIFER', 'WHITE', 'D15');

Explain It Like I'm Five

INSERT is putting a new card in a card box. VALUES is you writing the card yourself. INSERT SELECT is photocopying cards from another box. DEFAULT is “use the printed stamp the box already knows.” Identity is the box giving the card a new number so you do not pick one. FOR n ROWS is stuffing several cards in one handful. If the handful is ATOMIC, one bad card puts all of them back; NOT ATOMIC keeps the good cards.

Exercises

1. Write INSERT VALUES for a DEPT row, listing every NOT NULL column explicitly.
2. Insert analysts from EMP into a clone table using INSERT SELECT, skipping EMPNO values that already exist (NOT EXISTS).
3. Explain GENERATED ALWAYS vs BY DEFAULT for an identity EMPNO on INSERT.
4. Sketch COBOL host variables and an indicator for a nullable MGRNO insert.
5. Contrast ATOMIC and NOT ATOMIC CONTINUE ON SQLEXCEPTION for FOR 100 ROWS.