2

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)
0

1 Answer 1

10

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

Run the above as a script (F5), and not a statement (Ctrl+Enter).

2
  • Let us continue this discussion in chat.
    – cristid9
    May 14, 2017 at 21:58
  • When I ran the command in SQL Developer and got the result in the Script output window it was limited to 5000 rows. When I spooled the output I got all rows.
    – Björn
    Dec 11, 2020 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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