[SOLVED] Insert statement with blank values without defining all columns

Insert statement with blank values without defining all columns

I have a need to insert 100+ rows of data into a table that has 25 text columns. I only want to insert some data into those columns and the rest be represented by a white space.

(Note: Text fields on PeopleSoft tables are defined as NOT NULLABLE, with a single white space character used to indicate no data instead of null.)

Is there a way to write an insert statement that does not define all the columns along with the blank space. As an example:

INSERT INTO CUST.RECORD(BUSINESS_UNIT, PROJECT_ID, EFF_STATUS, TMPL, DESCR) VALUES('TOO1','PROJ1','A','USA00','USA00 CONTRACT');

For every other column in CUST.RECORD I'd like to insert ' ' without defining the column or the space in the insert.

Solution

One way is to set a Default value in table definition like this:

CREATE TABLE CUST.RECORD(
  id          NUMBER DEFAULT detail_seq.NEXTVAL,
  master_id   varchar2(10) DEFAULT ' ',
  description VARCHAR2(30)
);

Edit: for your table you can use :

alter table  CUST.RECORD modify( col2 varchar2(10) default ' ' );