INSERT SELECT in Firebird
firebird, firebird2.5, insert, select, sql
Solution
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`
Problem
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.