Inserting value into an identity column
In SQL INSERT Statement there are multiple options to take care of IDENTITY COLUMN.
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
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')
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')