At 11:04 PM 21/02/2005 -0700, you wrote:

>I have to insert a row into a Firebird SQL database, into a table that has a
>foreign key reference to a table of 'Categories'. The value that I'm know
>is the textual representation of the category, and so in order for me to
>insert the master record into the Primary table, I must first know the ID
>number of this Category to update record with the correct Foreign key ID.
>
>Without writing a SP to do this, what is the easiest way to do this in a
>single SQL Insert statement? Basically I know all the other fields to
>update except this one. Is it possible to combine a SELECT statement into
>an INSERT statement? Can anyone give an example of how this is normally
>done?

Normally, you'd know the category ID, as it's safer than the description,
which might change or be non-unique.

This is a bit topsy-turvey, but you could get it this way:

insert into aTable (ID, data1, data2, category_ID)
select
gen_id(gen_aTable, 1),
'something',
'something else',
(select id from category
where description = 'The Text You Have')
from rdb$database;

It's not the only way - it happens to be convenient if you don't have a
trigger to generate aTable's primary key. If you do, you could do this
instead:

insert into aTable (data1, data2, category_ID)
select
'something',
'something else',
ID
from category
where description = 'The Text You Have';

./hb