INSERT Statement by using SELECT

Posted on 2007-07-30
Last Modified: 2013-12-18
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
Question by:victory_in
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    INSERT INTO sometable ( col1, col2 ... )
    SELECT field1, field2...
    FROM emp;
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >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...
    LVL 27

    Accepted Solution

    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') ;
    LVL 28

    Assisted Solution

    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.

    Author Comment

    Sujith and Nav you both are right. thanks for your help. Will share the ponits.


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now