Re: dumping a table in sql insert format?

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 17 May 2007 23:00:09 +0200
Message-ID: <464CC259.4040604@gmail.com>

Mark Harrison schrieb:
> 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
>

Oracle's free SQL Developer can do it for you, provided you make some mouse clicks.
You still have do some editing like disable triggers before insert, alter session set cursor_sharing=force etc.

Best regards

Maxim Received on Thu May 17 2007 - 16:00:09 CDT