Home » SQL & PL/SQL » Client Tools » Generate Insert script for existing table data (Oracle9i)
Generate Insert script for existing table data [message #342159] Thu, 21 August 2008 10:22 Go to next message
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
Re: Generate Insert script for existing table data [message #342170 is a reply to message #342159] Thu, 21 August 2008 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, just use a SELECT 'INSERT ...' statement.

Regards
Michel
Re: Generate Insert script for existing table data [message #342176 is a reply to message #342170] Thu, 21 August 2008 11:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Generate Insert script for existing table data [message #342219 is a reply to message #342176] Thu, 21 August 2008 13:13 Go to previous messageGo to next message
Frank
Messages: 7877
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
Re: Generate Insert script for existing table data [message #342224 is a reply to message #342219] Thu, 21 August 2008 13:28 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
wheww lot of work, so that's the reason why dba's are paid sky-high!
Re: Generate Insert script for existing table data [message #342226 is a reply to message #342219] Thu, 21 August 2008 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A first step is T.Kyte's print_table function.

Regards
Michel
Re: Generate Insert script for existing table data [message #342339 is a reply to message #342159] Fri, 22 August 2008 01:26 Go to previous messageGo to next message
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
Re: Generate Insert script for existing table data [message #343628 is a reply to message #342159] Wed, 27 August 2008 12:19 Go to previous messageGo to next message
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
Re: Generate Insert script for existing table data [message #343772 is a reply to message #343628] Thu, 28 August 2008 02:03 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks to all involved for giving your valuable time.

Regards,
Oli
Re: Generate Insert script for existing table data [message #346429 is a reply to message #343772] Mon, 08 September 2008 09:43 Go to previous messageGo to next message
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/
Re: Generate Insert script for existing table data [message #346729 is a reply to message #346429] Tue, 09 September 2008 09:22 Go to previous messageGo to next message
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.
icon7.gif  Re: Generate Insert script for existing table data [message #523454 is a reply to message #342159] Sun, 18 September 2011 22:57 Go to previous messageGo to next message
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
Re: Generate Insert script for existing table data [message #523475 is a reply to message #523454] Mon, 19 September 2011 01:29 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What tool are you talking about, Sachin?
Re: Generate Insert script for existing table data [message #523600 is a reply to message #523475] Mon, 19 September 2011 08:13 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
The latest SQL developer does that. You can dump out a query result as a series of inserts, or even a .ldr file if the mood takes you Smile

It's under the 'export data' option. iirc it's a newer feature so make sure you have a current version.
Re: Generate Insert script for existing table data [message #523983 is a reply to message #523600] Wed, 21 September 2011 06:05 Go to previous messageGo to next message
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
Member
Yeah, as said, PL/SQL developer does that as well. We use that one, its pretty nifty, but not as expensive as TOAD.
Re: Generate Insert script for existing table data [message #524001 is a reply to message #523475] Wed, 21 September 2011 06:28 Go to previous messageGo to next message
sachinpanwar
Messages: 3
Registered: September 2011
Location: Dubai
Junior Member
Hi,
I am talking about PL/SQL Developer. You can export data as SQL file.
Re: Generate Insert script for existing table data [message #524003 is a reply to message #523454] Wed, 21 September 2011 06:31 Go to previous messageGo to next message
sachinpanwar
Messages: 3
Registered: September 2011
Location: Dubai
Junior Member
read it as :

data grid --> right click --> Export Result ->> SQL file
Re: Generate Insert script for existing table data [message #524008 is a reply to message #524003] Wed, 21 September 2011 06:40 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
I meant SQL Developer, it's another one, different from pl/sql developer Smile
Re: Generate Insert script for existing table data [message #524341 is a reply to message #524008] Fri, 23 September 2011 03:13 Go to previous message
mytony
Messages: 1
Registered: September 2011
Location: zhejaing
Junior Member
find some useful ways here,thanks
Previous Topic: Need help regarding notepad++ integration with plsql
Next Topic: SQL Developer Shortcut
Goto Forum:
  


Current Time: Fri Apr 18 16:48:51 CDT 2014

Total time taken to generate the page: 6.28591 seconds