|Subject||RE: [firebird-support] Is it possible to perform such insert?|
|Author||Svein Erling Tysvær|
>Can not I do something like this:No, it is not quite that simple. However, if you're on a recent Firebird version, you can do something like this:
>INSERT INTO MYTABLE SELECT EXPLICT_VAL1, EXPLICT_VAL2, T.*(ALL BUT FIRST TWO COLUMNS) FROM MYTABLE T WHERE T.RECORD_ID = 50;
execute block as
declare variable s varchar(2000);
declare variable s2 varchar(2000);
where rdb$relation_name = 'MYTABLE'
and rdb$field_name not in ('ID', 'DUMMY') into :s;
select 'INSERT INTO MYTABLE( FIRST_COLUMN, SECOND_COLUMN,' || :s || ') SELECT GEN_ID(MY_GENERATOR, 1), EXPLICIT_VAL, ' || :s || ' FROM MYTABLE WHERE RECORD_ID = 50'
execute statement s2;
Basically, what this is doing, is creating the insert statement dynamically through querying the rdb$relation_fields system table before executing the query. If you have lots of fields, you may have to increase the size of s and s2. I used GEN_ID to show how this could be included if you just want to modify a primary key and copy the rest. Feel free to modify execute block to include input parameters for EXPLICIT_VAL if you want some user input for the new record.