Easier than it appears. Just create a new table and import all those rows and records random selected and ordered by the RAND() SQL function:
CREATE TABLE new_table SELECT * FROM old_table ORDER BY RAND()
Of if you have created a table identical to the structure of the old one, use INSERT INTO instead:
INSERT INTO new_table SELECT * FROM old_table ORDER BY RAND()
That is of course if you want to preserve the primary key identification of each row, which is most likely what you want to do with old tables because of the legacy code and data entity relationships. However, if you want a grand new table with all the shuffled records completely rearranged in order as if it’s for a different application, you can ignore the primary key or ID by not importing the ID field of the old table.
For instance, you got ID, col1 and col2 in the old table as data fields. To create a grand new reordered or shuffled rows version of old table:
CREATE TABLE new_table SELECT col1, col2 FROM old_table ORDER BY RAND()
And a new primary key ID will be automatically assigned to each of the rows in the new table.
You should also read:
- SQL: INSERT INTO … SELECT … to generate or combine records from existing ones and insert into an existing table
- SQL: Select Random Rows or Records in MySQL
- MySQL: Deleting Duplicate Rows or Records | Selecting Unique Rows into A New Table
- Using SQL to Find records existing in one table but not in another
- MySQL: Update Multiple Rows or Records with One Single Query
Facebook
Twitter
Google Plus
{ 3 comments… read them below or add one }
Does this work? If you say SELECT *, RAND() FROM tablename, you get the same RAND value for each record. So when ordering by RAND() you get no ordering at all because everything ties!
I just realized that you might not be using SQL Server SQL. For SQL Server, use NEWID() instead of RAND().
Yep. I develop primarily with MySQL.