Insert statement with blank values without defining all columns

Question

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.

If you're just trying to save some typing for a single SQL statement then you're probably out of luck. But if this is a pattern you want to repeat for many inserts, or many tables, there are options that can help. But any solution would require creating PL/SQL objects, and would also depend on the database version. (For example, there might be a way to use polymorphic table functions, but that requires at least version 18c.) — Jon Heller, Mar 13 '21 at 22:33

Farshid Shekari · Accepted Answer · 2021-03-12T21:27:33.927

1

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 ' ' );

edited Mar 12 '21 at 21:27

answered Mar 12 '21 at 20:22

Farshid Shekari

2,391
4
27
47

That would work but the table exists and can't be dropped/recreated without far more work than just writing out the insert by hand. I will keep that in my back pocket for future use! – Cody Powell Mar 12 '21 at 20:31
You can use this https://stackoverflow.com/questions/21057275/alter-table-to-modify-default-value-of-column – Farshid Shekari Mar 12 '21 at 20:40
"One way is to set a Default value in table definition" Incorrect. As long as a column is nullable -- defined _without_ the 'not null' clause, it very will _can_ be inserted with no data at all. – EdStevens Mar 12 '21 at 20:57
@EdStevens look at this https://blog.pythian.com/adding-columns-with-default-values-and-not-null-in-oracle-11g/ – Farshid Shekari Mar 12 '21 at 21:05
@FarshidShekari - so what's your point? The article shows how oracle improved its internal handling about _adding_ a 'not null with default' column to a table. The OP is asking about not having to specify data for every column. That said, on second reading I do see where you wrote "One way is to set a Default" and somehow I read the "one" as "only" -- "only way is to ..." My apologies. – EdStevens Mar 12 '21 at 21:18
In Oracle `''` (zero-length string) is the same as `NULL` so `DEFAULT ''` is the same as `DEFAULT NULL`. – MT0 Mar 12 '21 at 21:24

score 0 · Answer 2 · answered Mar 12 '21 at 21:13

You do not have to supply a value for a specific column IF either condition is true:

The column is defined as nullable. That is, it was NOT defined with the 'not null' clause.

or

The column is defined with a default value

SQL> create table my_test (my_id number not null, 2 fname varchar2(10), -- nullable 3 dob date default sysdate -- default value 4 ) 5 ;

Table created.

SQL> -- SQL> -- only supplying value for my_id SQL> insert into my_test(my_id) values (1);

1 row created.

SQL> -- SQL> -- and see the results SQL> select * 2 from my_test;
```
  MY_ID FNAME      DOB
```
```
      1            12-MAR-21
```
1 row selected.

SQL> -- SQL> select my_id, 2 nvl(fname,'NULL'), 3 dob 4 from my_test;
```
  MY_ID NVL(FNAME, DOB
```
```
      1 NULL       12-MAR-21
```
1 row selected.

Insert statement with blank values without defining all columns

2 Answers2