Generating sql insert into for Oracle

Question

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

User · Answer

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

User · Answer

I have been searching for a solution for this and found it today  Here is how you can do it    Open Oracle SQL Developer Query Builder Run the query Right click on result set and export  http   i stack imgur com lJp9P png

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Generating sql insert into for Oracle

Examples related to sql

Examples related to oracle