Firebird sql to insert a typical record from another table with only one different field

开发者_如何学CI work on Firebird 2.5 and I have two tables, all their columns are similar except one has a primary key with auto increment and a not null foreign key field (A) for master table

I know I can use sql like this to insert all values from the two tables

 insert into table1 select * from table2 where somthing = 'foo'

but what about the field (A) is there any way to insert this value manually in the same sql statement ? as this is the only field need to be entered manually

Thanks

You can specify both the source and target fields explicitly (and you should; don't use select * unless you have a specific reason to):

insert into table1
(
    col1,
    col2,
    col3,
    col4
)
select
    col1,
    col2,
    col3,
    'foo'

from table2

where something = 'foo'

Came upon this post because I was looking for a solution to do the same, but without hard-coding the field names because the fields maybe added/removed and didn't want to have to remember to update the copy record procedure.

After googling around for awhile, I came up with this solution:

   select cast(list(trim(RDB$FIELD_NAME)) as varchar(10000))
      from RDB$RELATION_FIELDS 
      where RDB$RELATION_NAME = 'YOUR_TABLE' 
         and RDB$FIELD_NAME not in ('ID') -- include other fields to NOT copy
   into :FIELD_NAMES;

   NEW_ID = next value for YOUR_TABLE_ID_GENERATOR;

   execute statement '
      insert into YOUR_TABLE (ID,' || FIELD_NAMES || ')
      select ' || cast(:NEW_ID as varchar(20)) || ',' ||
         FIELD_NAMES || '
         from YOUR_TABLE
         where ID = ' || cast(:ID_OF_RECORD_TO_COPY as varchar(20));

Hope this saves some time for anyone else who comes across this issue!