Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2476
  • Last Modified:

INSERT Statement by using SELECT

Hello gentlemen,

I've emp table. By using "SELECT statement, I need to create a INSERT statement for this. How do I do that?In other words I've 10 records in EMP table.I need to create INSERT statement using those values.

I need 10 insert statement becuase there is 10 records in the table. Make sense....

Thank you so much
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
INSERT INTO sometable ( col1, col2 ... )
SELECT field1, field2...
FROM emp;
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I need 10 insert statement becuase there is 10 records in the table
as you see from above suggestion, you will need 1 INSERT statement to copy all 10 records at 1 time...
You should use a logic like the following. Change it based on your table structure. Note that for VARCHAR2 and DATE type columns you must have two single quotes in the select statement instead of a single one as shown in the example. You can spool this output to a text file by using the sqlplus "spool" command to create an insert script.
Alternatively, if you have any tools like TOAD or SQL Developer, you can export the data directly as insert scripts.

SQL> select * from a1;

        ID VAL
---------- --------------------
        10 one
        20 two
        40 four

SQL> select 'insert into a1(id, val) values (' || id || ', '''||val||''') ; ' stmt from a1;

insert into a1(id, val) values (10, 'one') ;
insert into a1(id, val) values (20, 'two') ;
insert into a1(id, val) values (40, 'four') ;
Naveen KumarProduction Manager / Application Support ManagerCommented:
if you have toad, then just right click on the data and click on save. In this
there is a radio button to save as insert statements and also you can save that to file.
In this case, toad will generate the insert statements according to your data.
victory_inAuthor Commented:
Sujith and Nav you both are right. thanks for your help. Will share the ponits.


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now