Creating a sample database with a few rows

[synapsevampire]

I'm hoping to populate a blank set of tables with data from a VERY large database with a few rows in each table to speed development against the database.

Assuming that there are no rules/constraints on the tables to prevent inserts from occuring in any order, can anyone point me in the directio to do this?

To simplify, I could perform an insert manually for the first few rows of a parent table, then pass to a SP the name of the parent table and the key to have it idenitfy the child tables and automatically perform inserts for those tables based on the key.

Ultimately I would prefer to just point at a database and state some limit to the rows to be populated and have to go do it all.

Are there any utilities that do this? Given the exellent reverse engineering tools out there, such as Embarcadero's ER Studio, the means for doing so is already in place, and I've had this sort of requirement many times, so others must as well.

Any thoughts?

Thanks,

-k

 

[SantaMufasa]

Vampire,

First, there is no need to cross post...the responders for the Oracle fora tend to stay on top of all posts in all Oracle fora, so since you are using Oracle 10g, I'll respond here, and to avoid confusion, I'll go flag your clone post in the Oracle 9i forum, once I've posted some ideas here.

The process that I would follow if I were you is:

1) Run the Primary Key-Foreign Key script that I posted in "How to list all the referential integrities within tables in a schema (thread186-1277043)".

2) For each of the base Parent Tables, run the following code from your DEV environment against your PROD schema tables:

CREATE TABLE <table_name> AS
SELECT * FROM prod.<table_name>
 WHERE rownum <= <how many rows you want>;

3) For each of the child tables, run the following code from your DEV environment against your PROD schema tables:

CREATE TABLE <child-table_name> AS
SELECT * FROM prod.<child-table_name>
 WHERE EXISTS (SELECT 'x' FROM dev.<parent-table_name>
                WHERE dev.<parent-table_name>.<PK col name>
                    = prod.<child-table_name>.<FK col name>);

Using the above code, you will get all the child rows for just the excerpt parent rows.

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[synapsevampire]

Unfortunately there are 2000 tables, hence the desire to find some form of automation.

Thanks for your time though.

I was thinking more of looping through the table objects and using the prmary key to determine which tables are related and then processing where rownum < X and then processing the related tables based on the same key.

I had hoped a wizardly sort might have worked all of this out already.

-k

 

[SantaMufasa]

Vampire,

If you are familiar with PL/SQL and its "EXECUTE IMMEDIATE" command, then, Yes, you can loop through the 2000 tables by using my script from "How to list all the referential integrities within tables in a schema (thread186-1277043)" as your driving query, then if "level = 1" (i.e., it is a parent table), then you "EXECUTE IMMEDIATE" the first "CREATE TABLE" statement, otherwise do an "EXECUTE IMMEDIATE" with the second "CREATE TABLE" statement.

If I wasn't late for leaving to do registration for our "Utah Oracle Users Group Fall Conference" (that starts in about 1/2 hour), I'd build a proof of concept for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]