CODEWITHSUNDEEP
db2primary-keysql-insert
Zenadia Groenewald
Zenadia Groenewald

Reputation: 117

DB2 insertion into a table with identity column

I'm currently trying to automate a DB2 database through shell scripts but there seems to be a problem with the INSERT operation.

The tables I create look like this:

CREATE TABLE "TRAINING1"
(ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (STARTS WITH 1 INCREMENT BY 1),
F1 VARCHAR(20) NOT NULL,
name VARCHAR(40) NOT NULL,
surname VARCHAR(40) NOT NULL,
CONSTRAINT pk_train1_id primary key(ID));

Now the creation works just fine for all similar tables, but when I attempt to insert data:

db2 "insert into TRAINING1 values ('hello', 'world', 'foo', 'bar')"

I get this error:

SQL0117N The number of values assigned is not the same as the number 
of specified or implied columns or variables. SQLSTATE=42802

As far as I understand, the primary key I specified should generate values automatically and cannot have values explicitly assigned to it. Out of curiosity I did this:

db2 "insert into TRAINING1 values (1, 'hello', 'world', 'foo', 'bar')"

and it then complains with this error:

SQL0798N A value cannot be specified for column "ID" which is defined 
as GENERATED ALWAYS. SQLSTATE=428C9

I'm still fairly new to DB2 but almost a week later, I still haven't found any solutions to this yet. I'm running DB2 Express Edition on a 64-bit Ubuntu virtual machine. Any thoughts on why it's doing this?

Thanks

Upvotes: 1

Views: 11739

Answers (1)

Fred Sobotka
Fred Sobotka

Reputation: 5332

In order to skip over a column in an INSERT statement, you must specify the columns that are not to be skipped:

db2 "insert into TRAINING1 (f1, name, surname) values ('hello', 'world', 'foo')"

Upvotes: 2

Related Questions