Create random records based on existing data with MariaDB

Question

On the basis of existing tables we need to create a randomized set. This is what we tried records are created but all fields are NULL;

CREATE PROCEDURE `create_data` ()
BEGIN
    declare done bool default false;
    declare anrede varchar(50);
    declare vorname varchar(50);
    declare nachname varchar(50);

    declare _vorname cursor for select anrede, vorname from vorname order by rand();
    declare _nachname cursor for select nachname from nachname order by rand();

    declare continue handler for not found set done = true;

    open _vorname;
    open _nachname;

    create_record: loop
        fetch _vorname into anrede, vorname;
        fetch _nachname into nachname;

        insert into lp values( null,
            anrede,
            vorname,
            nachname
        );
        if done then
            leave create_record;
        end if;

    end loop;
END

what did we miss here?

Answer 1

Please provide SHOW CREATE TABLE lp

The whole procedure can be simplified to one statement:

INSERT into lp 
       ( anrede, vorname, nachname )
    SELECT null,
        v.anrede,
        v.vorname,
        v.nachname
      FROM vorname AS v
      JOIN nachname AS n  ON TRUE
      ORDER BY RAND();

That could be a big table (size of vorname times size of nachname). If you want a smaller table (like what you were trying to do), then

INSERT into lp 
       ( anrede, vorname, nachname )
    SELECT
        ( SELECT anrede FROM vorname ORDER BY RAND() LIMIT 1 ) AS anrede,
        ( SELECT vorname FROM vorname ORDER BY RAND() LIMIT 1 ) AS vorname,
        ( SELECT nachname FROM nachname ORDER BY RAND() LIMIT 1 ) AS nachname
      LIMIT 1000;

This second version mixes up the name parts randomly and produces exactly 1000 names.

Using INSERT...SELECT...; avoids the need for declaring variables and looping. And is probably much faster.

I assume the NULL was aimed for an AUTO_INCREMENT; leaving it out (as I did) has the same effect.