CODEWITHSUNDEEP
oracle-databasetoad
Tpk43
Tpk43

Reputation: 331

How to generate table scripts with data in Toad for Oracle - 9.7.2

I wanna create a script for table that should include

  1. Create Table statement
  2. Data in the table
  3. Sequence in the table(Only sequence code)
  4. And Trigger associated to it

I have added Sequence and trigger for auto increment ID, I searched but I couldn't get enough answers for Sequence in trigger.

Upvotes: 1

Views: 2123

Answers (1)

Littlefoot
Littlefoot

Reputation: 142715

I understand you, partially.

In order to get CREATE TABLE statement, choose that table and on right-hand side of the screen navigate to the "Script" tab - there it is. Apart from CREATE TABLE, it contains some more statements (such as ALTER TABLE in order to add constraints, CREATE INDEX and your number 4 - CREATE TRIGGER).

As of the sequence: it is a separate object, which is not related to any table. One sequence can be used to provide unique numbers for many tables, so - I'm not sure what is it that you are looking for.

In order to get data from that table, right-click table name; in menu choose "Export data" >> "Insert statements". That'll create bunch of INSERT INTO commands. That's OK if table is small; for large ones, you'll get old before it finishes.

The last sentence leads to another suggestion: why would you want to do it that way? A proper option is to export that table, using either Data Pump or the Original EXP utility.

[EDIT]

After you insert data "as is" (i.e. no changes in ID column values), disable trigger and run additional update. If we suppose that sequence name is MY_SEQ (create it the way you want it, specifying its start value etc.), it would be as simple as

update your_table set id = my_seq.nextval;

Once it is done, enable the trigger so that it fires for newly added rows.

Upvotes: 1

Related Questions