A quick SQL query to generate example data

Question

I need to populate a currently empty table with a hundred or so fake records to simulate logins over the past two years to test my code with.

The login table schema looks like:

CREATE TABLE `Logins` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `User_ID` int(11) NOT NULL,
  `Date_Login` datetime NOT NULL,
  `Location` enum('site','admin') NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I really am new at SQL in general, so, I don't have the slightest idea what the query should look like past

INSERT INTO `Logins` (`User_ID`,`Date_Login`,`Location`) VALUES ...

What I need is insert N entries (lets say 100) into Logins so that

• User_ID draws its values from the Users table's ID field
• Date_Login should be between 2 years ago and now
• Location should alternate between 'site' and 'admin', but with 'site' more heavily weighted (say 80% of the time).

Hopefully, I can glean some SQL-fu to help with similar problems in the future :D

Thanks!

(I'm using MySQL 5.1)

Answer 1

If you want to use a full SQL solution, here is how you can create a loop: http://dev.mysql.com/doc/refman/5.1/en/iterate-statement.html To get a random date, you can use RAND() in combination with +, * and some timestamps.

Answer 2

Here is an SQL statement to insert a single row into the Logins table. You can run this repeatedly (e.g. in a stored procedure) to get multiple records. You have to run it multiple times to get multiple records because if you increase LIMIT 1 to LIMIT 10 you will get 10 records but the User_ID values will be the same for each record.

INSERT INTO `Logins` (`User_ID`, `Date_Login`, `Location`)
SELECT
  users.ID AS `User_ID`,
  DATE_SUB(NOW(), INTERVAL FLOOR(1 + (RAND() * (365 * 2))) DAY) AS `Date_Login`,
  IF(RAND() > 0.8, 'admin', 'site') AS `Location`
FROM users
ORDER BY RAND()
LIMIT 1;

Normally ORDER BY RAND() is bad style because it is inefficient, but this isn't a performance-sensitive task.