I'm not sure this is the best way to go about this or not, but the need arose for me to do this recently and this is how I did it. This tutorial is focused on the scenario of "closing" one blog and combining onto another one, rather than creating an all-new blog.
**Please backup both databases and keep them separate and safe before going through these steps
Step 1: Export the wp_posts table as .SQL
The first thing you need to do is get an .SQL file of the wp_posts table out of the blog you going to close. This is really pretty easy if you have access to phpMyAdmin. Go to the Export tab, select the wp_posts table, and "Save as File". This will allow you to save the SQL file out to your desktop. Perfect. There is probably similarly easy ways of doing this from within the shell if you are saavy that way.
Step 2: Do it again for the other blog
We don't actually need this SQL file, but we need to know the last post ID in that file. So scroll down all the way to the bottom of it and see what it is.
Step 3: Remove the CREATE TABLE section
That table is already going to be there when you import, so you can remove this section in your SQL file that you got from Step 1.
Step 4: Renumbering
This is the time-consuming part. You are going to need to give new ID's to every single entry in that first SQL file, starting at the next number after the last number you got from Step 2. If you try and import the SQL without doing this, there will be a ton of duplicates and the import won't work. Unless you accidentally have it write over the duplicates and then you are screwed =(. Just do this.
If you don't do this:
Step 5: Move your images
It is likely that your posts will contain a ton of links to images on it's own domain. If you want to actually totally kill the old domain name, you will need to move these images over to the new server. Hopefully they are in a nice directory all together so you can move them and combine them with images already on the server you are moving to. Go ahead and move them over. The trick here will be to do a FIND/REPLACE on file paths in the SQL file. Do something like this.
REPLACE ALL: http://NEW-domain.com/wp-content/uploads/
Step 6: Importing
Now that you have your nice new fixed up SQL file, it's ready for importing. Go to phpMyAdmin for the site where these posts are to be combined. Go to the Import tab, browse for your file, and press Go. Barring any errors (like the duplicate error shown above) your new posts will be instantly integrated into this site!
What you are going to lose
Comments, Categories, Users.... Pretty much everything that isn't simply the posts themselves. You could probably use a similar technique to the one described above and save that stuff too, but I think it would be much more complicated. I was satisfied with the results of doing it this way, since there really wasn't much comment activity on the "old" blog.
- This process is kind of nasty with all the stuff you lose, it is much better to just think things through before starting multiple blogs. It is a lot easier to focus your attention on a single blog and give it all your love than it is to split that love between two. Just like children. (kidding.)
- Take care not to check the DROP TABLE checkbox when exporting your SQL. That can be very useful for moving/rewriting tables, but not in this case. If your SQL includes that command, all your old posts will be lost and replaced by the new ones. (Bad.)
- Playing with live databases is dangerous business. These databases contain all the content of your blog. If something goes foul, you could lose everything. Please BACKUP BOTH DATABASES first and then you are all good.
UPDATE Kevin at Blogging Tips pointed out to me that there is a WAY easier and WAY better way to do this. Use the Export/Import features built right into WordPress. Oh boy, do I feel smart now =).