1

What is the simplest way to generate insert statements fro every entry in a table? For a table with 3 rows, I need to generate 3 insert statements.

For a table with n rows I need to write to a file n insert statements.

For example, for row (foo, bar, baz) I need to write to that file the statement:

insert into desired_table values (foo, bar, baz)
  • For a table with n rows I need to write to a file n insert statements. For example, for row (foo, bar, baz) I need to write to that file the statement: insert into desired_table values (foo, bar, baz). – cristid9 May 14 '17 at 12:32
  • This however is useful for a small set of rows. for more than few hundred you might find SQLLDR may yield better performance. One of the reason of this recommendation is you will be hard hitting shared pool as these inserts don't use bind variables. – Raj May 15 '17 at 15:28
8

In SQL Developer, use the /*insert*/ "hint".

select /*insert*/ * from t1;

REM INSERTING into T1
SET DEFINE OFF;
Insert into T1 (C1,C2) values (1,2);
Insert into T1 (C1,C2) values (2,3);
Insert into T1 (C1,C2) values (3,4);

Another useful "hint":

select /*csv*/ * from t1;

"C1","C2"
1,2
2,3
3,4

Starting with version 4.1, the "hint" the below also works:

set sqlformat insert
select * from t1;

The output can be spooled as well:

set sqlformat insert
spool C:\Users\balaz\Desktop\insert.sql
select * from t1;
spool off
| improve this answer | |
  • Are hints disabled by default in SQLDeveloper? – cristid9 May 14 '17 at 12:47
  • 4
    No, they are not. You need to run the above as a script (F5), and not a statement (Ctrl+Enter). – Balazs Papp May 14 '17 at 12:48
  • One more stupid question. How can I save the data returned by select /*insert*/ * from t in a variable. I mean, to save each row returned by that select statement in a variable. I have tried to use a cursor like for v_row in (select /*insert*/ * from users) loop dbms_output.put_line(v_row); end loop. But it doesn't seem to work. Here I've just tried to print them to see if a coursor is the way to solve this problem. – cristid9 May 14 '17 at 12:58
  • 1
    @cristid9 This is a feature specific to SQL Developer, the output is formatted at the client side. You can not save this into a variable. – Balazs Papp May 14 '17 at 13:08
  • 1
    @cristid9 Yes, you can use spool for that, see my update. – Balazs Papp May 14 '17 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.