Insert blank line

Question

It is so simple that it was probably already asked, but I could not find it (if it apologizes, I apologize).

I would like to insert an empty row into the table so that I can get my identifier (primary key generated by the insert trigger) via ExecuteScalar. Data is added to it later in my code.

My question is this: is there a specific insertion syntax for creating an empty record? or should I go with regular insert syntax like INSERT INTO values ​​(list of all columns) (null for each column) ??

Thanks for the answer.

UPDATE:. In Oracle ExecuteScalar, INSERT returns 0. The final answer is a combination of what was published below. First you need to declare a parameter and raise it with RETURNING.

INSERT INTO TABLENAME (ID) VALUES (DEFAULT) RETURNING ID INTO :parameterName 

See more details.

Answer 1

Basically, to insert a row where the values ​​for all columns are NULL except the primary

key column value, you could execute a simple insert :

 insert into your_table(PK_col_name) values(1); -- 1 for instance or null 

The trigger before insert, which is responsible for filling the primary key column, will be

override the value in the values clause of the insert , leaving you with

null entry except PK value.

Answer 2

You do not need to specify each individual column, but you may not be able to create an “empty” record. Check the NOT NULL constraints on the table. If not (not including the primary key constraint), then you will need to specify only one column. Like this:

 insert into my_table ( some_column ) values ( null ); 

Do you know about RETURNING? You can return this PC back to the calling application when you execute INSERT.

 insert into my_table ( some_column ) values ( 'blah' ) returning my_table_id into <your_variable>; 

I would question the approach. Why create an empty string? This could mean that there are no restrictions on this table; this is bad if you need good, clean data.