Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Question Unanswered: insert muliple rows into a table at ones

    hi iam relatively new to DB design...

    so i wanna know if v can insert multiple rowns into a DB table with only 1 insert statement

    cheers

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    If you want to populate the new table with data from other existing tables, you can do that :

    insert into new_table(field1, field2,...)
    select (fieldA, fieldB,...)
    from ...
    where...
    ;

    Note that field1 and fieldA must have the same datatype (or the fieldA datatype may be a subtype of the field1 datatype), same thing for field2 and fieldB, etc...

    You can also create a new populated table by doing so :

    CREATE TABLE table1 AS select ... from ... where...;

    Hope it helps.

    Regards,

    RBARAER

  3. #3
    Join Date
    Sep 2004
    Posts
    2
    I just want to populate newly created table with some data..i dont have anyother tables with same data..so is there any way i could populate table..instead of writing insert command for each row....

    cheers
    salguti

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    In that case, as far as I know, you will have to do one insert at a time.
    In MySQL, you have

    "INSERT INTO table1(field1, field2) VALUES (v1, v2),(v3,v4),...;"

    , but as far as I know, that does NOT exist in Oracle, which means that you won't be able to do all inserts in one time with a SQL query.

    So, if you want to do these inserts quickly, I suggest that you read raw data in a program (from a text file for example, or generated in the program), and loop on inserts... you have many ways to do that programmatically depending on the language you choose (PL/SQL, Java, C/C++, C#, ...) and if you want to do it VERY QUICKLY or not.

    I may be able to help you depending on the language you choose.

    Regards,

    RBARAER

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    you can use array processing in pl/sql. it allows you to populate an array and pass it once to an insert statement. This technique also exists in Pro*C.

    search BULK & FORALL in pl/sql manual (example in manual):
    DECLARE
    TYPE num_tab IS TABLE OF NUMBER;
    deptnums num_tab;
    BEGIN
    SELECT deptno BULK COLLECT INTO deptnums FROM DEPT;

    FORALL i IN 1..deptnums.COUNT
    INSERT INTO emp_by_dept
    SELECT empno, deptno FROM emp WHERE deptno =
    deptnums(i);

    FOR i IN 1..deptnums.COUNT LOOP
    -- Count how many rows were inserted for each department; that is,
    -- how many employees are in each department.
    dbms_output.put_line('Dept '||deptnums(i)||': inserted '||
    SQL%BULK_ROWCOUNT(i)||' records');
    END LOOP;

    dbms_output.put_line('Total records inserted =' || SQL%ROWCOUNT);
    END;
    /
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •