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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
