Inserting an empty row

Question

This is so simple it has probably already been asked, but I couldn't find it (if that's the case I'm sorry for asking).

I would like to insert an empty row on a table so I can pick up its ID (primary key, generated by an insert trigger) through an ExecuteScalar. Data is added to it at a later time in my code.

My question is this: is there a specific insert syntax to create an empty record? or must I go with the regular insert syntax such as "INSERT INTO table (list all the columns) values (null for every column)"?

Thanks for the answer.

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

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

Check this out link for more info.

Answer 1

You would not have to specify every single column, but you may not be able to create an "empty" record. Check for NOT NULL constraints on the table. If none (not including the Primary Key constraint), then you would only need to supply one column. Like this:

insert into my_table ( some_column )
values ( null );

Do you know about the RETURNING clause? You can return that PK back to your calling application when you do the INSERT.

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

I would question the approach though. Why create an empty row? That would/could mean there are no constraints on that table, a bad thing if you want good, clean, data.

Answer 2

Basically, in order to insert a row where values for all columns are NULL except primary

key column's value you could execute a simple insert statement:

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

The before insert trigger, which is responsible for populating primary key column will

override the value in the values clause of the insert statement leaving you with an

empty record except PK value.