Create 1 million row in an Oracle Database?

insert into user (select rownum,'first','last','address','company' from dual connect be level<1000000);

If you want random values for names and addresses use dbms_random.string.  The docs have the usage.

mmm so I have the following from Google:

  SELECT TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(DATE '1999-01-01','J')
                                    ,TO_CHAR(DATE '2015-12-31','J')
                                    )
                    ),'J'
               ) FROM DUAL;

Select allOpen in new window

I also have this for Strings:

DBMS_RANDOM.STRING
   opt  IN  CHAR,
   len  IN  NUMBER)
  RETURN VARCHAR2;

Select allOpen in new window

but now how would the query be to mix both of them? (it is the first time I see something like this can be achievable)

>>but now how would the query be to mix both of them?

You just use dbms_random for the column and data type you want.  If you want a number, use VALUE.  If you want a string use STRING.

If you want a random length string, use VALUE for the len parameter of the STRING function.

I suggest you play around with simple examples using sqlplus to learn what they are doing:
select dbms_random.value(100,200) from dual;
select dbms_random.string('a',10) from dual;select dbms_random.string('a',dbms_random.value(10,20)) from dual;

Run each one several times to get the idea.

Ok, I have play with it and think I understand the idea, but when I tried to apply to my query it fails saying there is a missing parenthesis, can you please check my syntax?

If I run the query without DBMS_RANDOM it does it with no issues:

INSERT INTO tblUser 
  (id, FirstName, LastName, MailAddress, Company) 
VALUES
    (
    '1', 
    'Oscar',
      'Garin', 
      'mrgari@gmail.com', 
      '01-01-2015');

Select allOpen in new window

If I do it replacing 1 column (FirstName) to match the random function, it fails:

INSERT INTO tblUser 
  (id, FirstName, LastName, MailAddress, Company) 
VALUES
    (
    '1', 

        DBMS_RANDOM.string('L' 20)
          (SELECT * FROM
              (SELECT FirstName FROM tblUser ORDER BY DBMS_RANDOM.VALUE
          )
          WHERE ROWNUM = 1),

      'Garin', 
      'mrgari@gmail.com', 
      '01-01-2015');

Select allOpen in new window

INSERT INTO ... VALUES ... is not the correct syntax.

INSERT INTO ... SELECT ... is.

No idea what you are trying to do with the SELECT from tblUser but it probably isn't going to work.  It will jusst select the first row and use that for everything so why bother.

Your columns also need to match.

You specify 5 on the insert side but look like you are trying to select 6.


Try this but it is untested:

INSERT INTO tblUser 
  (id, FirstName, LastName, MailAddress, Company) 
select 
    '1', 
        DBMS_RANDOM.string('L' 20),
      'Garin', 
      'mrgari@gmail.com', 
      '01-01-2015' from dual;

Select allOpen in new window

Not working, same error "missing right parenthesis"

also this working query does not work when I add your predicate "connect be level<1000000);"

INSERT INTO tblUser 
  (id, FirstName, LastName, MailAddress, Company) 
VALUES
    (
    '1', 
    'Oscar',
      'Garin', 
      'mrgari@gmail.com', 
      '01-01-2015') connect be level<1000000);

Select allOpen in new window

This does not work ? I have modified slightwv's select:
INSERT INTO tblUser
select
    '1',
        DBMS_RANDOM.string('L' 20),
      'Garin',
      'mrgari@gmail.com',
      '01-01-2015' from dual;

Also "connect be" ought to be "connect by".

Excellent!