How to avoid selecting next value before inserting

user_19783144 0
Viewed 81

I need help with a SQL command. I would like to create several data in the database, but the table has a "counter" (generator). Before I create data in a new row, I have to call up and increase the counter with "SELECT NEXT VALUE".

What command do I have to enter if I use insert into for the generator once but want to insert data at the same time (values)

firebird sql
2 Answers

You can use NEXT VALUE FOR <sequence-name> directly in your insert statement, so there is no need to select it separately. If you also need the generated value for subsequent statements, you can use the RETURNING clause to return the value.

For example

insert into some_table (id, other_column) 
   values (next value for seq_some_table_id, 'some value') 
   returning id

In addition, you can even simplify this further if you use an identity column or a before insert trigger to generate the value for you.

For example:

Identity column

create table some_table (
  id integer generated by default as identity primary key,
  other_column varchar(50)
);

or generated using trigger

create table some_table (
  id integer primary key,
  other_column varchar(50)
);

create sequence seq_some_table_id;

set term #;
create trigger some_table_bi_id
active before insert on some_table
as
begin
  if (new.id is null) then
    new.id = next value for seq_some_table_id;
end#
set term ;#

The insert then no longer needs to specify the ID column:

insert into some_table (other_column) 
   values ('some value') 
   returning id
user_4668620

It very simple you can add ID column or any name with int datatype and for that you enable identity as true, it will auto increment when new data is inserted. No need to any code for that.

Note: No need to pass value for ID, SQL do itself.

user_142918090
Related