OraFAQ Forum: Client Tools » Generate Insert script for existing table data

Home » Other » Client Tools » Generate Insert script for existing table data (Oracle9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Generate Insert script for existing table data [message #342159]

Thu, 21 August 2008 10:22

Olivia
Messages: 519
Registered: June 2008

Senior Member

Is theree any way to generate the insert script (with the data)
for an existing table.

In Toad, we can generate the DDL for a particular table.
Just curious if it can be done using any Tool.

Regards,
Oli

Report message to a moderator

Re: Generate Insert script for existing table data [message #342170 is a reply to message #342159]

Thu, 21 August 2008 10:54

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, just use a SELECT 'INSERT ...' statement.

Regards
Michel

Report message to a moderator

Re: Generate Insert script for existing table data [message #342176 is a reply to message #342170]

Thu, 21 August 2008 11:13

Olivia
Messages: 519
Registered: June 2008

Senior Member

Thanks for the reponse.

How it will generate the insert script? The statement you provided is for inserting data from one table to another only.

I want to know if insert sql script can be generated for a table containg data.

e.g, something like extract (Employee table)

then script generated should be say insert.sql and should have below statements:

INSERT INTO EMPLOYEE VALUES ( 1, 'MICHEL','CADOT',20)
/
INSERT INTO EMPLOYEE VALUES ( 2, 'OUTLAND','JIM',30)
/
..

assuming Employee table has 4 columns

Regards,
Oli

[Updated on: Thu, 21 August 2008 11:15]

Report message to a moderator

Re: Generate Insert script for existing table data [message #342184 is a reply to message #342176]

Thu, 21 August 2008 11:31

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select 'insert into dual values ('''||dummy||''');' from dual;
'INSERTINTODUALVALUES('''||DUM
------------------------------
insert into dual values ('X');

1 row selected.

Regards
Michel

Report message to a moderator

Re: Generate Insert script for existing table data [message #342219 is a reply to message #342176]

Thu, 21 August 2008 13:13

Frank
Messages: 7901
Registered: March 2000

Senior Member

To do this 'in general' for arbitrary tables, you will have to do quite some work.
Start out by querying all_tab_columns or user_tab_columns, if you only want tables for the current user.
Using the info from that table, start building your sql-string:
add quotes surrounding string fields, add to_date around date-fields (that you converted to strings first), etc.

Not really a straight-forward thing.
Especially, because the next thing you will want is to include some sort of where clause. Then you will want to extract sets of tables and finally you will end up wanting a mechanism to extract test-cases from one db to insert into another..
Seen it, been there, broke my teeth on it (multiple times) Wink

Report message to a moderator

Re: Generate Insert script for existing table data [message #342224 is a reply to message #342219]

Thu, 21 August 2008 13:28

ehegagoka
Messages: 493
Registered: July 2005

Senior Member

wheww lot of work, so that's the reason why dba's are paid sky-high!

Report message to a moderator

Re: Generate Insert script for existing table data [message #342226 is a reply to message #342219]

Thu, 21 August 2008 13:35

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A first step is T.Kyte's print_table function.

Regards
Michel

Report message to a moderator

Re: Generate Insert script for existing table data [message #342339 is a reply to message #342159]

Fri, 22 August 2008 01:26

Olivia
Messages: 519
Registered: June 2008

Senior Member

Quote:

SQL> select 'insert into dual values ('''||dummy||''');' from dual;
'INSERTINTODUALVALUES('''||DUM
------------------------------
insert into dual values ('X');

1 row selected.

I needed to generate the insert script for a table and the above query did worked fine.

Thanks to Michel and Frank providing more info on it.

Regards,
Oli

Report message to a moderator

Re: Generate Insert script for existing table data [message #343628 is a reply to message #342159]

Wed, 27 August 2008 12:19

tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx

Junior Member

Quote:

In Toad, we can generate the DDL for a particular table.
Just curious if it can be done using any Tool.

There is a tool called PL/SQL Developer that does a *very* good job of creating such INSERT scripts for a list of tables specified.

That particular feature is for "Export Tables...", and hence the final script has "CREATE TABLE" statements by default.

The INSERT INTO statements could be specified for all rows of the table list, or for just a few (depending on the constraints) or avoided completely.

tyler_durden

Report message to a moderator

Re: Generate Insert script for existing table data [message #343772 is a reply to message #343628]

Thu, 28 August 2008 02:03

Olivia
Messages: 519
Registered: June 2008

Senior Member

Thanks to all involved for giving your valuable time.

Regards,
Oli

Report message to a moderator

Re: Generate Insert script for existing table data [message #346429 is a reply to message #343772]

Mon, 08 September 2008 09:43

Wisser
Messages: 1
Registered: September 2008

Junior Member

There is also an open source tool called Jailer which creates INSERT scripts.
It extracts rows of a specified table and all rows associated with them. One can use it to export small but referentially correct portions of data from productive databases and import the data into development or test environments.

see http://jailer.sourceforge.net/

Report message to a moderator

Re: Generate Insert script for existing table data [message #346729 is a reply to message #346429]

Tue, 09 September 2008 09:22

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

In Oracle's free development tool SQL Developer you can write any query, and then right click on the result set.
Amon other options is the option to save the data set as a set of Insert statements into the tavble of your choice.

Report message to a moderator

Re: Generate Insert script for existing table data [message #523454 is a reply to message #342159]

Sun, 18 September 2011 22:57

sachinpanwar
Messages: 3
Registered: September 2011
Location: Dubai

Junior Member

Just run the select scrit first on the table you want to create insert script
e.g. select * from table table where <your condition if any>

Now go to data grid --> right click --> save as SQL file

It would create the insert script for you..

Thanks,
Sachin Panwar

Report message to a moderator