SQL Plus - Insert statement

Posted on 2011-10-08
Last Modified: 2012-05-12
I need to write insert statement but one values will be calculate from other fields and records

any example for that ?
Question by:egovernment
    LVL 23

    Expert Comment

    - maybe something like this?:

    INSERT INTO table1 (field1, field2) SELECT field1, (field2 + 1) FROM table2
    LVL 23

    Expert Comment

    - if you can provide sample data, and how its being caculated it will much clearer. does the value came from a field in different table or the same table?
    LVL 31

    Accepted Solution

    See attached.
    LVL 67

    Assisted Solution

    The traditional approach is to construct the necessary SELECT step by step, and then just stuff that into an INSERT. Something complicated like this:
    insert into tbl1 (keyfield, data1, data2, data3)
    select t.keyfield+1, o.data1, o.data2, o.data1+o.data2
    from (select max(keyfield) as keyfield from tbl) t,
    othertbl o where o.data3 > 1000

    Open in new window

    This will determine the maximum of an (integer) key field, increment it, and insert a record. This will, of course, only work correctly for a single record, but it is just an example.
    LVL 31

    Expert Comment

    I wish it were true that there is no reason to post my responses as an attachment, and that's precisely how I always initially respond. However, I don't know whether it's due to the extremely restrictive firewall under which I work, but I would say about 90% of the time I try to submit a direct response, it gets timed out, but an attachment will go through. This has been extremely frustrating to me as I have tried to submit solutions that were correct only to have to copy them to a file, save them and re-submit as an attachment only to have someone else get credit for submitting the same solution minutes earlier than my attachment. I expressed my displeasure with this a long time ago when EE made some substantial changes to the site causing my need to use the attachment method. I am trying to submit this comment directly now. Hopefully, it will go through.

    Author Comment

    I will return all answers and come back

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    This article describes some very basic things about SQL Server filegroups.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    761 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

    7 Experts available now in Live!

    Get 1:1 Help Now