So recently I had to insert 10,000 entries into a table in a MySQL database. I wanted to select a group of users by some criteria and then grab their username and insert the username and a code xyz. My criteria gave me about 10,000 users out of about 230,000 users. There are way to many to do manually, so how to do it then.

First thought, pay my 14 year old son $20.00 and a milk shake and have him do it manually. This would require a couple days to complete and would only be about 40% accurate, causing me more time trying to fix his mistakes. Throw that idea on the back burner.

My next thought was to write a quick Perl script. This would use the Perl DBI, run a quick select statement, throw the returned data into an array. Then run a for loop on the array and run 10,000 insert queries. This would work but would take some time and resources. Again there had to be an easier way.

Hello SQL. The SQL INSERT command allows for you to insert a select statement. This will update everything quick and easy. Below is the resulting query.

INSERT INTO user_codes (username, code) SELECT username, ‘xyz’ FROM user_login WHERE inbr in (‘1’, ‘3’);

This did the trick. The key was in the select statement. By placing a constant in the select statement, I was able to complete the work quickly using very little resources. Brute work is not always the way to go. Slow down, think and use a little finesse. You will most likely find a quick and elegant solution to your problem.