INSERT SELECT in Firebird

Question

I'm new to firebird and I have verious issues. I want to insert various lines into a table selected from another table.

Here's the code:

/*CREATE GENERATOR POS; */
SET GENERATOR POS TO 1;

SET TERM ^;

create trigger BAS_pkassign
   for MATERIAL
active before insert position 66

EXECUTE BLOCK
AS

  declare posid bigint;
  select gen_id(POS, 1)
  from RDB$DATABASE
  into :posid;

BEGIN



END

SET TERM ; ^


INSERT INTO MATERIAL ( /*ID */ LOCATION, POSID, ARTID, ARTIDCONT, QUANTITY )
SELECT  1000, ':posid', 309, BAS_ART.ID, 1
FROM    BAS_ART
WHERE   BAS_ART.ARTCATEGORY LIKE '%MyWord%'

The ID should autoincrement from 66 on. The posid should autoincrement from 1 on.

Actually it is not inserting anything.

I'm using Firebird Maestro and have just opened the SQL Script Editor (which doesnt throw any error message on executing the script).

Can anybody help me?

Thanks!

Additional information:

The trigger should autoincrement the column "ID" - but I dont know how exactly I can change it so it works.. The ':posid' throws an error using it :posid but like this theres no error (I guess its interpretated as a string). But how do I use it right?

I dont get errors when I execute it. The table structure is easy. I have 2 tables: 1.

 Material (
ID (INTEGER),
Location (INTEGER),
POSID (INTEGER),
ARTID (INTEGER),
ARTIDCONT (INTEGER),
QUANTITY (INTEGER),
OTHERCOLUMN (INTEGER)) 

and the 2. other table

BAS_ART (ID (INTEGER), ARTCATEGORY (VARCHAR255))

-> I want to insert all entries from the table BAS_ART which contain "MyWord" in the column ARTCATEGORY into the MATERIAL table.

Solution 1

I don't understand why you need the trigger at all.

This problem:

I want to insert all entries from the table BAS_ART which contain "MyWord" into the MATERIAL table

Can be solved with a single insert ... select statement.

insert into material (id, location, posid, artid, quantity)
select next value for seq_mat_id, 1000, next value for seq_pos, id, 1
from bas_art
where artcategory = 'My Word';

This assumes that there is a second sequence (aka "generator") that is named seq_mat_id that provides the new id for the column material.id

Solution 2

For most of my answer I will assume a very simple table:

CREATE TABLE MyTable (
   ID BIGINT PRIMARY KEY,
   SomeValue VARCHAR(255),
   posid INTEGER
)

Auto-increment identifier

Firebird (up to version 2.5) does not have an identity column type (this will be added in Firebird 3), instead you need to use a sequence (aka generator) and a trigger to get this.

Sequence

First you need to create a sequence using CREATE SEQUENCE:

CREATE SEQUENCE seqMyTable

A sequence is atomic which means interleaving transactions/connections will not get duplicate values, it is also outside transaction control, which means that a ROLLBACK will not revert to the previous value. In most uses a sequences should always increase, so the value reset you do at the start of your question is wrong for almost all purposes; for example another connection could reset the sequence as well midway in your execution leaving you with unintended duplicates of POSID.

Trigger

To generate a value for an auto-increment identifier, you need to use a BEFORE INSERT TRIGGER that assigns a generated value to the - in this example - ID column.

CREATE TRIGGER trgMyTableAutoIncrement FOR MyTable
ACTIVE BEFORE INSERT POSITION 0
AS 
BEGIN 
    NEW.ID = NEXT VALUE FOR seqMyTable;
END

In this example I always assign a generated value, other examples assign a generated value only when the ID is NULL.

Getting the value

To get the generated value you can use the RETURNING-clause of the INSERT-statement:

INSERT INTO MyTable (SomeValue) VALUES ('abc') RETURNING ID

INSERT INTO ... SELECT

Using INSERT INTO ... SELECT you can select rows from one table and insert them into others. The reason it doesn't work for you is because you are trying to assign the string value ':pos' to a column of type INTEGER, and that is not allowed.

Assuming I have another table MyOtherTable with a similar structure as MyTable I can transfer values using:

INSERT INTO MyTable (SomeValue)
  SELECT SomeOtherValue
  FROM MyOtherTable

Using INSERT INTO ... SELECT it is not possible to obtain the generated values unless only a single row was inserted.

Guesswork with regard to POSID

It is not clear to me what POSID is supposed to be, and what values it should have. It looks like you want to have an increasing value starting at 1 for a single INSERT INTO ... SELECT. In versions of Firebird up to 2.5 that is not possible in this way (in Firebird 3 you would be able to use ROW_NUMBER() for this).

If my guess is right, then you will need to use an EXECUTE BLOCK (or a stored procedure) to assign and increase the value for every row to be inserted.

The execute block would be something like:

EXECUTE BLOCK
AS
  DECLARE posid INTEGER = 1;
  DECLARE someothervalue VARCHAR(255);
BEGIN
  FOR SELECT SomeOtherValue FROM MyOtherTable INTO :someothervalue DO
  BEGIN
    INSERT INTO MyTable (SomeValue, posid) VALUES (:someothervalue, :posid);
    posid = posid + 1;
  END
END

Without an ORDER BY with the SELECT the value of posid is essentially meaningless, because there is no guaranteed order.

Comments

[Mark Rotteveel]

The only reason for a trigger that I can think of here would be to generate an auto-increment value for the id column. Note that the insert in your answer always assigns 1000 to this column instead of the location column as in the question.

[a_horse_with_no_name]

@MarkRotteveel: yes I am aware about the constant value for material.id But so does the example insert ... select in the question so I assumed that's what the intention is. But it would be very easy to populate that column from a sequence as well.

[Mark Rotteveel]

In the question the ID column is commented out, but now I am just nitpicking ;)

[a_horse_with_no_name]

@MarkRotteveel: ah! I somehow missed that.

[number5]

Thank you very much the next value thing together with a sequence worked. I didnt need a trigger at all. You were right. You forgot the ARTIDCONT column but i pasted it in and then it was working perfectly. Now on every execute I l0ok up the current ID and put it in RESTART WITH <id>. May be theres an easier solution with a trigger but it is working.