DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: table rows

  1. #1
    Join Date
    Sep 2001
    Location
    UK
    Posts
    45

    table rows

    Hi,

    I have a requirement where i have insert the same set of rows
    in a table based on some input parameter
    eg table A has 10 rows and parameter =3
    I want to insert the same 10 rows thrice in the same table

    which is the best way of doing it using SQL only


    Thnx in advance

  2. #2
    Join Date
    Sep 2005
    Posts
    3
    Form a cartesian:

    consider my original table:

    Code:
    SQL> select *
      2    from mhe_foo
      3  /
    
            ID TEXT
    ---------- -------------------------
             1 One
             2 Two
             3 Three
             4 Four
    Now, form a cartesian with dual:
    Code:
    SQL> select M.*
      2    from mhe_foo M
      3       , ( select level from dual connect by level <= &g )
      4  /
    Enter value for g: 1
    old   3:      , ( select level from dual connect by level <= &g )
    new   3:      , ( select level from dual connect by level <= 1 )
    
            ID TEXT
    ---------- -------------------------
             1 One
             2 Two
             3 Three
             4 Four
    
    SQL> /
    Enter value for g: 3
    old   3:      , ( select level from dual connect by level <= &g )
    new   3:      , ( select level from dual connect by level <= 3 )
    
            ID TEXT
    ---------- -------------------------
             1 One
             2 Two
             3 Three
             4 Four
             1 One
             2 Two
             3 Three
             4 Four
             1 One
             2 Two
             3 Three
    
            ID TEXT
    ---------- -------------------------
             4 Four
    
    12 rows selected.
    This dual..level trick is useful if you need a table with n rows and you don't want to query dictionary tables. It only works from 9i and up.

    MHE

Posting Permissions

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


Click Here to Expand Forum to Full Width