How to select random values from a table and store it in another table in mysql

Summary

In this tutorial we will learn how to select random values from a table and store it in another table in mysql.

To retrieve random values from a table we use rand() function in mysql. rand() function fetches random values from a table. Lets take an example.

How to select random values from a table

We have one table called department. Now we want to retrieve all records randomly we use rand() function.

 mysql> select * from department order by rand() ;

Here order by clause is used to sort the randomly generated data.

Now we can place the limit to fetch particular no of rows like the below query;

mysql> select * from department order by rand() limit 3;

Here we place limit 3 to fetch 3 rows instead of 8 rows.

select random values from a table and store it in another table in mysql

Now we are going to store our randomly generated records to another table. we have department table from which we have fetched our random records. Now we will create another table named backupdept and in this table we are going to the fetched data.

Now to create table in mysql we need to execute the below query.

mysql> create table backupdept ( deptno int ,deptname varchar(20),ename varchar(20),sal int);

Query OK, 0 rows affected (0.03 sec)

To insert data from one table to another table we will use insert into select statement as below.

mysql> insert into backupdept (deptno,deptname,ename,sal) select  * from department order by rand() ;

Conclusion

In this tutorial we have learned how to select random values from a table and store it in another table in mysql.

SHARE

Admin

• 
• 
• 
• 
•