Re: Using sequences in SQL text files

On 20 févr. 08, at 08:57, HHB wrote:
>
> Thank you all for your help.
> Let me summer what I'm trying to do:
> I have an empty database that I want to populate it with data.
> I created SQL text files, categories.sql, books.sql ....
> As I'm a Java guy, I use Apache Ant SQL taks to run those SQL text
> files.
> So, I run categories.sql file first, then books.sql
> It seems to me that Brian's solution supposed that I have all SQL
> insert
> statements are in one file, this isn't my case (actually, I have
> many SQL
> files that
> I want to run, merging them in one SQL isn't practical).
> So, is it possible to do so:
> ----
> categories.sql
> don't insert an id here
> INSERT INTO categories ('..', '..' ...) VALUES ('..', '..', ...);
> INSERT INTO categories ('..', '..' ...) VALUES ('..', '..', ...);
> ----
> books.sql
> INSERT INTO books (category_id, '..', ...) VALUES
> (CAST(currval('categories_id_seq') AS INT), '..', ...);
> INSERT INTO books (category_id, '..', ...) VALUES
> (CAST(currval('categories_id_seq') AS INT), '..', ...);
> ----
> Thank you all for your time.
>

With this, all books will be created in only 1 category : the last
created, and i think this is not what you want.

If you really want to separate books insertion from categories
insertion, your INSERT statements for books must be smarter and look
like :

INSERT INTO books (category_id, ....)
SELECT category_id, ....
FROM categories WHERE category_name = '';

ex:
INSERT INTO books (category_id, name)
SELECT category_id, 'Lord of the rings'
FROM categories WHERE category_name = 'Fantasy';

(assuming you have a 'Fantasy' category)

Hope this helps.

Tom