sql - How to insert 100 rows in a table?

Question

I have a table ENJOY(id int not_null auto_increment,name char(30)) I have to insert 100 rows with 3 values of name (cn,pogo,disney) with the count of ids increasing

Answer 1

Looks like you're looking for some kind of a row generator. If that's so, see whether this helps.

Note line #9: I'm inserting 3 rows of each name (to make the result simpler and easier to read); you'd insert 100 rows.

SQL> create table enjoy (id int, name varchar2(10));

Table created.

SQL> insert into enjoy (id, name)
  2  with names as
  3    (select 'cn' name from dual union all
  4     select 'pogo'    from dual union all
  5     select 'disney'  from dual
  6    )
  7  select column_value, name
  8  from names cross join table(cast(multiset(select level from dual
  9                                            connect by level <= 3
 10                                           ) as sys.odcinumberlist));

9 rows created.

SQL> select * from enjoy;

        ID NAME
---------- ----------
         1 cn
         2 cn
         3 cn
         1 pogo
         2 pogo
         3 pogo
         1 disney
         2 disney
         3 disney

9 rows selected.

SQL>