How to Generate Table Scripts with Data in Toad for Oracle - 9.7.2
I Wanna Create a Script for Table That Should Include Create Table Statement Data in the Table Sequence in the Table(Only Sequence Code) and Trigger Associated...
I wanna create a script for table that should include
- Create Table statement
- Data in the table
- Sequence in the table(Only sequence code)
- 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.
1 Answer
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.
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
