inserting without specifying EVERY value

[jayvaughn]

so i'm wanting to do an insert to a table and only specify a couple of key values. the table has each column defined as "NOT NULL", but no default declared. Does this mean I have to specify a value for each one of those on my insert. If I don't it says null value not allowed in column.

my statement

exec sql
insert into loanalt4 (a4loan)
values (:i_loanNumber);

error
Null values not allowed in column or variable A4SEQ.

a4seq in table...
A4_SEQ FOR COLUMN A4SEQ NUMERIC(2,0) NOT NULL,

[Scott Klement]

You could use ALTER TABLE to give the column a default value, then it will use that default. (or you can allow nulls...) if you don't, then yes you'll have to provide it each time you insert a new row. But if you give it a default, then you don't have to provide it...

The system has to do something with the column... it can set it to null or a default or a value you provide. But it has to do something...

[jayvaughn]

figured that might be the case - issue is that there are like 95 columns in this table - uhhhhhhh - so may as well bite the bullet and define them and default value in the insert statement. I suppose if the ddl had default values specified, this wouldn't be necessary - correct?

[jayvaughn]

would i be facing same situation if i declared an f-spec and took the native approach? - i tried that and got what seemed to be the same error????

[TedHolt]

Going native won't help, Jay. The database manager doesn't care where the data came from. That's the nice thing about using constraints, triggers, etc. to enforce data integrity -- there's no way around it.

[B.Hauser]

Since it seems to be an (embedded) SQL Program, you could do the following trick:
Define an external (array) data structure for your file.
Clear or initialize this array data structure, fill your key columns into this array data structure and use:

Exec SQL Insert into YourTable :NbrRows Rows
                 Values(:YourArrayDS);

Birgitta