Oracle: insert without columns specification

Question

I have an oracle table with a sequence and a trigger to autoincrement a column. Now I want to make an insert. Normally I should write:

INSERT INTO table (column1, column2,...) VALUES (value1, value2)

but I just want to insert a record with no default values. How to do this in Oracle?

`Mysql`: INSERT INTO table () VALUES ()

`Mssql`: INSERT INTO table default VALUES 

`Oracle:` INSERT INTO table (column1, column2,...) VALUES (default, default,...)

Is this the only way? I have to list all columns?

score 9 · Accepted Answer · answered Jul 15 '11 at 09:54

9

INSERT INTO table (column1) VALUES (default);

The rest will be defaulted!

answered Jul 15 '11 at 09:54

Tony Andrews

129,880
21
220
259

score 6 · Answer 2 · answered Jul 15 '11 at 13:51

In Oracle you don't HAVE to specify the columns but not doing so will leave you open to introducing bugs as and when your table definition changes.

You could insert with:

INSERT INTO t VALUES (value1, value2, value3);

This assumes the table t has three columns

It is far better and supportable to insert with:

INSERT INTO t (column1, column2, column3) VALUES (value1, value2, value3);

I wouldn't use PL/SQL (if you can help it) as you introduce context switching from PL/SQL to SQL and then back to PL/SQL again.

score 3 · Answer 3 · 2011-07-16T23:07:54.133

3

Is this the only way? I have to list all columns?

Yes. And it's good practice to always specify all columns in an INSERT statement that you want to supply values for.

edited Jul 16 '11 at 23:07

answered Jul 15 '11 at 09:50

No, you don't *have* to list *all* columns. – Dave Costa Jul 15 '11 at 16:08

score 3 · Answer 4 · answered Jul 15 '11 at 16:13

3

I missed this part on the first read:

I have an oracle table with a sequence and a trigger to autoincrement a column.

So I assume there is on PK column populated using the sequence, and the others all have default values. Given that, I would do this:

INSERT INTO table (pk_column) VALUES (NULL);

The trigger will override the NULL value (and if it doesn't for some reason, the insert will fail); and the other columns will be populated using defaults.

answered Jul 15 '11 at 16:13

Dave Costa

47,262
8
56
72

1

You could add a `default null` to the pk, and then the insert would be `insert into table (pk_column) values (default)`. – Shannon Severance Jul 15 '11 at 17:19
@ShannonSeverance Shannon solution worked 100%. Thank you Shannon. You saved me some time. `INSERT INTO table (ID) VALUES (DEFAULT);` – Bartosz Olchowik Aug 18 '22 at 15:46

score 1 · Answer 5 · answered May 25 '18 at 06:28

There is only one case where you don't need to specify COLUMNS in Oracle and that is when you are inserting rows with values specified for all columns and that too in same sequence as the definition of table. In all other cases one needs to specify column list.

Example :

create table tt (id number, name varchar2(30));
insert into tt values (1,'AC');
insert into tt values ('2',1);

Especially note 2nd insert statement -> First parameter '2' is provided as String and expectation is number in ID column. Here does necessary casting and inserts 2 in ID column. But user may have wanted to insert '2' in NAME column and 1 as ID. With specifying column list Oracles helps us to avoid such errors.

Also there will be rarely any situation now a days where you DON"T need to specify column list because almost all tables created mostly have an auto-incremented unique ID column which is auto-filled via Trigger or because of IDENTITY columns and you provide values of rest of the columns only that means those column list must be provided else it will result in error.

In case this unique ID is generated and inserted by an application say generated in java application via Random number generation or Hibernate then as all column would be inserted column list might be avoided.

Oracle: insert without columns specification

5 Answers5