DB2 SQL DB GENERATED SEQUENCE : IDENTITY COLUMN

Inserting value into an identity column

In SQL INSERT Statement there are multiple options to take care of IDENTITY COLUMN.

  1. With out giving any value for IDENTITY COLUMN.

    When INSERT statement is used with column name and map values using “VALUES” clause, Omit both IDENTITY COLUMN and corresponding value from query and SQL will assign value for IDENTITY COLUMN automatically.

    INSERT INTO INVOICE (ORDER_NUMBER) VALUES('ORD1234')
    INVOICE_NUMBER ORDER_NUMBER
    100,001 ORD1234
  2. With using DEFAULT clause for value of IDENTITY COLUMN.

    When INSERT statement is used with IDENTITY COLUMN, “DEFAULT” can be used as value for that column.

    INSERT INTO INVOICE (INVOICE_NUMBER, ORDER_NUMBER) VALUES(DEFAULT, 'ORD2001')

    Or when column names are not included in INSERT statement DEFAULT need to be used for IDENTITY COLUMN’s position.

    INSERT INTO INVOICE VALUES(DEFAULT, 'ORD2002')
  3. With user given values.

    When IDENTITY COLUMN is added with “BY DEFAULT” clause other than above 2 methods there is one more option to insert value.

    invoice_number INTEGER GENERATED BY DEFAULT AS IDENTITY "

    “BY DEFAULT” tells SQL to generate automatic value for IDENTITY COLUMN only if no user value is specified for that IDENTITY COLUMN and If user value is specified use that value.

    INSERT INTO INVOICE (INVOICE_NUMBER, ORDER_NUMBER) VALUES(200, 'ORD2001')                           

Leave a Reply

Your email address will not be published. Required fields are marked *