Re: dumping a table in sql insert format?

DA Morgan <damorgan_at_psoug.org> wrote:
> Mark Harrison wrote:
> > What's the best way to dump some tables into a file
> > such that the data is in a series of insert statements?
> >
> > I would like to set up some regression tests for my
> > database, and this is the seed data that is needed
> > after the schema has been created.
> >
> > TIA!
> > Mark
> >
>
> SELECT 'INSERT INTO t VALUES (' || srvr_id || ')' FROM servers;
>
> Wouldn't external tables be easier?

Well, here's my goal, any suggestions appreciated...

I want to be able to easily set up and tear down a development or test environment. Ideally such things as sequences will all be initialized to known values, so that regression testing is as easy as possible, e.g. doing simple string comparisons (dates/times are the main trouble here).

So I have a script which creates tables, indexes, etc. This I'm getting via a Toad "export/schema" dump, which is working nicely. Since it generates a distinct file for each object, it plays nicely with our revision control system, and it has a wrapper script that sources all the other scripts in the appropriate order.

So all that I'm missing (I think) are some statements at the end to initialize some of our "meta" style data, e.g.

insert into types (name,id) values('tla',1);
insert into types (name,id) values('p4',2);
insert into types (name,id) values('df',3);

and I'd like to autogenerate these at the same time as I generate the schema export script if I can.

Thanks!
Mark

-- 
Mark Harrison
Pixar Animation Studios