Subject | SQL help for dataset initialization |
---|---|
Author | gregatacd@yahoo.ca |
Post date | 2007-02-09T22:40:10Z |
Environment: Firebird 2.0
I have 2 tables in a master/child relationship as follows:
CREATE TABLE MASTER (
MASTERID BIGINT NOT NULL,
DESC VARCHAR(200)
);
CREATE TABLE CHILD (
CHILDID BIGINT NOT NULL,
MASTERID BIGINT NOT NULL,
DESC VARCHAR(200)
);
MASTERID is the PK in MASTER, and CHILDID is the PK in CHILD, and
CHILD.MASTERID is properly set up as a foreign key to MASTER.
Both PKs are auto-populated with generators via an insert trigger.
Now, when I create my database, I want to automatically populate these
tables with data right from the start by executing as SQL script.
For example:
INSERT INTO MASTER (DESC) VALUES ("FIRST");
INSERT INTO MASTER (DESC) VALUES ("SECOND");
INSERT INTO MASTER (DESC) VALUES ("THIRD");
This works fine for the master table, since the trigger takes care of
the PK assignment. The problem comes when I want to insert the CHILD
entries. I don't know what the ID is for the MASTER entry...
INSERT INTO MASTER (DESC) VALUES ("FIRST");
INSERT INTO CHILD (MASTERID, DESC)
VALUES (<dont know this!>, 'FIRST CHILD OF FIRST');
INSERT INTO CHILD (MASTERID, DESC)
VALUES (<dont know this!>, 'SECOND CHILD OF FIRST');
I know that in FB 2.0, there is an extended syntax
INSERT INTO MASTER (DESC) VALUES ("FIRST") RETURNING MASTERID INTO
:varMASTERID;
INSERT INTO CHILD (MASTERID, DESC)
VALUES (:varMASTERID, 'FIRST CHILD OF FIRST');
INSERT INTO CHILD (MASTERID, DESC)
VALUES (:varMASTERID, 'SECOND CHILD OF FIRST');
But this requires a variable definition, which is not available in a SQL
script such as this.
I suppose I could use the new EXECUTE BLOCK statement for this, but is
there a limitation as to the size of an EXECUTE BLOCK statement? If I
have a relatively large dataset to initialize my database with that has
a significant number of relationship interdependencies that require
variable scope to be quite large, then this may not scale particularly well.
Another option is to write an application (C++ and IBPP, for example)
that does this work for me, but then anyone who is responsible for
dealing with the default data set would have to deal with C++ instead of
just living in the SQL world...
What is the 'best practice' for this scenario?
Thx!
Greg
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
I have 2 tables in a master/child relationship as follows:
CREATE TABLE MASTER (
MASTERID BIGINT NOT NULL,
DESC VARCHAR(200)
);
CREATE TABLE CHILD (
CHILDID BIGINT NOT NULL,
MASTERID BIGINT NOT NULL,
DESC VARCHAR(200)
);
MASTERID is the PK in MASTER, and CHILDID is the PK in CHILD, and
CHILD.MASTERID is properly set up as a foreign key to MASTER.
Both PKs are auto-populated with generators via an insert trigger.
Now, when I create my database, I want to automatically populate these
tables with data right from the start by executing as SQL script.
For example:
INSERT INTO MASTER (DESC) VALUES ("FIRST");
INSERT INTO MASTER (DESC) VALUES ("SECOND");
INSERT INTO MASTER (DESC) VALUES ("THIRD");
This works fine for the master table, since the trigger takes care of
the PK assignment. The problem comes when I want to insert the CHILD
entries. I don't know what the ID is for the MASTER entry...
INSERT INTO MASTER (DESC) VALUES ("FIRST");
INSERT INTO CHILD (MASTERID, DESC)
VALUES (<dont know this!>, 'FIRST CHILD OF FIRST');
INSERT INTO CHILD (MASTERID, DESC)
VALUES (<dont know this!>, 'SECOND CHILD OF FIRST');
I know that in FB 2.0, there is an extended syntax
INSERT INTO MASTER (DESC) VALUES ("FIRST") RETURNING MASTERID INTO
:varMASTERID;
INSERT INTO CHILD (MASTERID, DESC)
VALUES (:varMASTERID, 'FIRST CHILD OF FIRST');
INSERT INTO CHILD (MASTERID, DESC)
VALUES (:varMASTERID, 'SECOND CHILD OF FIRST');
But this requires a variable definition, which is not available in a SQL
script such as this.
I suppose I could use the new EXECUTE BLOCK statement for this, but is
there a limitation as to the size of an EXECUTE BLOCK statement? If I
have a relatively large dataset to initialize my database with that has
a significant number of relationship interdependencies that require
variable scope to be quite large, then this may not scale particularly well.
Another option is to write an application (C++ and IBPP, for example)
that does this work for me, but then anyone who is responsible for
dealing with the default data set would have to deal with C++ instead of
just living in the SQL world...
What is the 'best practice' for this scenario?
Thx!
Greg
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com