[sql] Generating sql insert into for Oracle

The only thing I don't have an automated tool for when working with Oracle is a program that can create INSERT INTO scripts.

I don't desperately need it so I'm not going to spend money on it. I'm just wondering if there is anything out there that can be used to generate INSERT INTO scripts given an existing database without spending lots of money.

I've searched through Oracle with no luck in finding such a feature.

It exists in PL/SQL Developer, but errors for BLOB fields.

Use a SQL function (I'm the author):

• https://github.com/teopost/oracle-scripts/blob/master/fn_gen_inserts.sql

Usage:

select fn_gen_inserts('select * from tablename', 'p_new_owner_name', 'p_new_table_name')
from dual;

where:

p_sql            – dynamic query which will be used to export metadata rows
p_new_owner_name – owner name which will be used for generated INSERT
p_new_table_name – table name which will be used for generated INSERT

p_sql in this sample is 'select * from tablename'

You can find original source code here:

• http://dbaora.com/oracle-generate-rows-as-insert-statements-from-table-view-using-plsql/

Ashish Kumar's script generates individually usable insert statements instead of a SQL block, but supports fewer datatypes.

Oracle's free SQL Developer will do this:

http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

You just find your table, right-click on it and choose Export Data->Insert

This will give you a file with your insert statements. You can also export the data in SQL Loader format as well.

You might execute something like this in the database:

select "insert into targettable(field1, field2, ...) values(" || field1 || ", " || field2 || ... || ");"
from targettable;

Something more sophisticated is here.

If you have to load a lot of data into tables on a regular basis, check out SQL Loader or external tables. Should be much faster than individual Inserts.

I have been searching for a solution for this and found it today. Here is how you can do it.

1. Open Oracle SQL Developer Query Builder

2. Run the query

3. Right click on result set and export

   http://i.stack.imgur.com/lJp9P.png

You can do that in PL/SQL Developer v10.
1. Click on Table that you want to generate script for.
2. Click Export data.
3. Check if table is selected that you want to export data for.
4. Click on SQL inserts tab.
5. Add where clause if you don't need the whole table.
6. Select file where you will find your SQL script.
7. Click export.

If you have an empty table the Export method won't work. As a workaround. I used the Table View of Oracle SQL Developer. and clicked on Columns. Sorted by Nullable so NO was on top. And then selected these non nullable values using shift + select for the range.

This allowed me to do one base insert. So that Export could prepare a proper all columns insert.

You can also use MyGeneration (free tool) to write your own sql generated scripts. There is a "insert into" script for SQL Server included in MyGeneration, which can be easily changed to run under Oracle.