Oracle : Export select statement result set as INSERT SQL Statements similar to SQL developer export
Solution 1
I just found a simple solution for my problem using oracle hint ("insert"). This automatically take care the data type as well. My table has only string and numeric data types, so it works fine for me. I have not tested the solution for other data types. However, I hope it would work for other data types as well.
set linesize 2000
set pagesize 10
spool "c:\myoutput.txt";
select /*insert*/ * from SAMPLE_TABLE;
spool off;
Solution 2
as a script you say...
Use SQLcl - it's a command-line interface for SQL Developer.
You can create a .bat or .sh script to launch SQLcl, run your query, spool it to a file, no need to write any code.
set pagesize...
set head...
set feedback...
spool...
set sqlformat insert
select * from sample_table;
spool off
exit
Solution 3
set sqlformat insert
In my case initially it didn't work in SQL developer as the version was version 3.2 and resulted with message in the script output:
oracle unknown set option sqlformat insert
As i upgraded to the SQL developer 4.2 and above and using "Run Script" (F5) i was able to export the select records as the insert scripts.
Solution 4
You can do it with an for loop and dbms output as you want, even as merge statements like Toad, just make the buffer big enough:
BEGIN
FOR r_cur IN ( SELECT column_one,
column_two,
column_three,
column_value
FROM some_table
WHERE column_one LIKE 'something%'
ORDER BY column_one, column_two, column_three)
LOOP
DBMS_OUTPUT.put_line (
'MERGE INTO some_table A USING
(SELECT\n
''' || r_cur.column_one || ''' as column_one,
''' || r_cur.column_two || ''' as column_two,
''' || r_cur.column_three || ''' as column_three,
''' || r_cur.column_value || ''' as column_value
FROM DUAL) B
ON (A.column_one = B.column_one and A.column_two = B.column_two and A.column_three = B.column_three)
WHEN NOT MATCHED THEN
INSERT (
column_one, column_two, column_three, column_value)
VALUES (
B.column_one, B.column_two, B.column_three, B.column_value)
WHEN MATCHED THEN
UPDATE SET
A.column_value = B.column_value;
' );
END LOOP;
END;
/
![notionquest](https://i.stack.imgur.com/7Si7m.jpg?s=256&g=1)
Comments
-
notionquest about 2 years
I am looking for a solution to create the SQL INSERT statements using PL/SQL script for the select statement result set. Looking for similar feature available in the SQL Developer tool (export --> format insert) but I want the solution as script rather than using any tool(s).
I have referred the below solution. However, I would like to know whether any better way to do it as the solution is old and not very simple.
EXPORT AS INSERT STATEMENTS: But in SQL Plus the line overrides 2500 characters!
-
notionquest almost 8 yearsThanks! Actually, the script doesn't take data type into consideration.
-
fleetmack almost 8 yearsI'm unclear on what you mean. You need it to take data type into consideration? Assuming the DDL is the same for this table in both the source & target, then data type shouldn't be an issue as all fields in the source copy of the table already meet the necessary constraints. I may be reading this entirely wrong, forgive me if that is so!
-
notionquest almost 8 yearsExample : If the string value in the column has spaces, the insert script wouldn't work if it is not enclosed with single quotes.
-
fleetmack almost 8 yearsRight - good point -- you'd have to do it like this: with mytest as (select 'hello there' as myfield from dual) select 'insert into mytable values ('''|| myfield||''');' from mytest; I'll edit my original post to reflect character strings
-
notionquest almost 8 yearsThanks for your help! I have just found a simple solution using oracle hint.
-
thatjeffsmith about 6 yearsSQL developer and SQLcl