How to quickly create rewrite rules for all WordPress posts

This post describes a quick way to create .htaccess rewrite rules after permalinks change from /%post_id% to /%year%/%monthnum%/%day%/%postname%/

Using phpMyAdmin execute SQL query:

SELECT id AS ID, date_format( post_date, "%Y" ) AS Y, date_format( post_date, "%c" ) AS M, date_format( post_date, "%d" ) AS D, post_name AS name
FROM `wp_posts`
WHERE post_status = "publish"
ORDER BY `wp_posts`.`id` ASC

Export results to CSV file with columns separated by “;” character and without header in the first row.

$ head wp_posts.csv
12;2010;11;29;how-to-access-cli-on-linksys-srw
42;2010;11;29;how-to-use-imagemagick-effects-with-coppermine
99;2010;11;30;having-problems-with-some-ie-only-web-interface-using-firefox
100;2010;12;01;nginx-proxy-and-real-ip-address
101;2010;12;01;nginx-and-gandi-ssl-certificate
102;2010;12;02;https-everywhere
107;2010;12;03;ruby-and-rmagick-quote-with-polaroid-like-effect
108;2010;12;04;zen-photo-polaroid-like-effect
110;2010;12;03;zen-photo-and-nginx-rewrite-rules
112;2010;12;03;linux-like-environment-for-windows

After the file is stored in the local file system you can use awk to generate rewrite rules:

$  awk -F\; '{print "Rewriterule ^" $1 "$ https://sleeplessbeastie.eu/" $2 "/" $3 "/" $4 "/" $5 "/ [R=301,L]"}' wp_posts.csv
Rewriterule ^12$ https://sleeplessbeastie.eu/2010/11/29/how-to-access-cli-on-linksys-srw/ [R=301,L]
Rewriterule ^42$ https://sleeplessbeastie.eu/2010/11/29/how-to-use-imagemagick-effects-with-coppermine/ [R=301,L]
Rewriterule ^99$ https://sleeplessbeastie.eu/2010/11/30/having-problems-with-some-ie-only-web-interface-using-firefox/ [R=301,L]
Rewriterule ^100$ https://sleeplessbeastie.eu/2010/12/01/nginx-proxy-and-real-ip-address/ [R=301,L]
Rewriterule ^101$ https://sleeplessbeastie.eu/2010/12/01/nginx-and-gandi-ssl-certificate/ [R=301,L]
Rewriterule ^102$ https://sleeplessbeastie.eu/2010/12/02/https-everywhere/ [R=301,L]
Rewriterule ^107$ https://sleeplessbeastie.eu/2010/12/03/ruby-and-rmagick-quote-with-polaroid-like-effect/ [R=301,L]
Rewriterule ^108$ https://sleeplessbeastie.eu/2010/12/04/zen-photo-polaroid-like-effect/ [R=301,L]
Rewriterule ^110$ https://sleeplessbeastie.eu/2010/12/03/zen-photo-and-nginx-rewrite-rules/ [R=301,L]
Rewriterule ^112$ https://sleeplessbeastie.eu/2010/12/03/linux-like-environment-for-windows/ [R=301,L]
[...]