Re: How to export table contents in sql statements

On Sun, 08 Oct 2000 20:31:55 +0200, "Dipl.-Informatiker Khamis Abuelkomboz" <khamis_at_knuut.de> wrote:

>I have two simple questions (I use oracle/mySQL):
>
>1. How can I export the schema of a table into SQL statments,
> like
>
>CREATE TABLE Animal (
> ID NUMBER(6) PRIMARY KEY,
> name VARCHAR2(20) UNIQUE,
> legs NUMBER(3),
> insertionDate DATE);
>
>CREATE SEQUENCE AnimalIDs
>
>2. How can I export the contents of tables to SQL statemtns, so
>that I can load those in a different database, like
>
>INSERT INTO Animal VALUES(AnimalIDS.NEXTVAL, 'cow', 4, SYSDATE);
>INSERT INTO Animal VALUES(AnimalIDS.NEXTVAL, 'tiger', 4, SYSDATE);
>INSERT INTO Animal VALUES(AnimalIDS.NEXTVAL, 'spider', 8, SYSDATE);
>
>thanks,
>khamis
>

Oracle has always been a bit short on tools. There are two ways to do this
1) download Toad (http://www.toadsoft.com) startup the schema browser, navigate to the affected table, right click, and you are there. Choose 'Create script' and 'Export' respectively. You can do this for an entrire schema, but I don't remember the exact menu options from the top of my head.
2) use Oracle export. The export will contain both the create statement and the insert statements.
To isolate the create statements do the following imp (or imp73 or imp80, if you are on wintel) <username/password> filename=<your export> full=y show=y log=<any filename> The create statements will be dumped in the file used in the log=

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Oct 14 2000 - 10:16:04 CDT