CODEWITHSUNDEEP
sqloracle-database
reg
reg

Reputation: 43

Inserting random names PL/SQL

How could I insert random names into another table ? For example :

select firstname,lastname from table1;
create table2 (newfirstname varchar2(20),newlastname varchar2(20));
insert into table2 select newfirstname, random(newlastname);

How could I generate new people using table1's infos ?
So , my question is , how do I insert random "lastnames" from table1 into table2 to obtain a new fullname(newfirstname+newlastname) from table2 being different of (firstname+lastname) from table1. I am using sql developer ,pl/sql .

Upvotes: 1

Views: 2764

Answers (3)

Marcelo reis
Marcelo reis

Reputation: 1

  SELECT
    aa.nome,
    aa.sobrenome,
    bb.nome,
    bb.sobrenome,
    CASE 
        WHEN DBMS_RANDOM.VALUE(1,50) > 20 THEN aa.nome || bb.sobrenome
        ELSE
         bb.nome || aa.sobrenome
    END AS new_name
  FROM
    (
      SELECT
          SubStr(b.nome,1,InStr(b.nome,' ')) nome,  
          SubStr(b.nome,InStr(b.nome,' '))  sobrenome,
          sexo
      FROM 
          cadastro b
      WHERE
          empresa_id=4 
      ORDER BY 
          DBMS_RANDOM.VALUE(1,800000)
    ) aa,
    (
      SELECT 
          SubStr(b.nome,1,InStr(b.nome,' ')) nome,  
          SubStr(b.nome,InStr(b.nome,' '))  sobrenome,
          sexo
      FROM 
          cadastro b
      WHERE
          empresa_id=4               
      ORDER BY
        DBMS_RANDOM.VALUE(1,800000 )
    ) bb
  WHERE  
      aa.sexo=bb.sexo

Upvotes: 0

Mike
Mike

Reputation: 2005

insert into table2
select A.firstname, B.lastname
  from table1 A, table1 B
 where not exists(select 1 from table1 C
                   where C.firstname=A.firstname and C.lastname=B.lastname)

Add random ordering and/or count limitation if need.

Upvotes: 1

Eugene Bartosh
Eugene Bartosh

Reputation: 324

I believe you should have numeric ID field in table2, like table2.id, then 

select  
(select MIN(table2.id) from table2) as minid, (select MAX(table2.id) from table2) as maxid, 
(select table2.firstname from table2 where table2.id >= dbms_random.value(minid,maxid) order by table2.id limit 1) as rnd_firstname, 
(select table2.lastname from table2 where table2.id >= dbms_random.value(minid,maxid) order by table2.id limit 1) as rnd_lastname;

will give you random combination of firstname + lastname from table2.

Upvotes: 0

Related Questions