Help populating new table with data from another table

[kjtocool]

Hi there, I don't think this question is as simple as my title makes it out to be!

 

Basically, I want to create a table with the following statement/structure:

 

CREATE TABLE Articles (
article_ID int(10) PRIMARY KEY AUTO_INCREMENT,
articleName varchar(100) NOT NULL,
author varchar(100) NOT NULL,
articleType varchar(30) NOT NULL,
date date,
body longtext NOT NULL,
grade char(2),
source varchar(60),
sourceURL varchar(255)
)

 

And I want to populate that table with data from a table that has the following structure:

 

Table Name = Articles
Serial int(11) auto_increment NOT NULL
ArticleName varchar(100) NOT NULL
ArticleURL varchar(160) NOT NULL
Author varchar(100) NOT NULL
Comment varchar(200) NOT NULL
Category varchar(30) NOT NULL
forDefault varchar(30) NOT NULL
relatedItem varchar(100) NOT NULL
thedate date NULL
thecontent longtext NULL
theid bigint(20) NOT NULL
grade char(2) NOT NULL
source varchar(60) NOT NULL
link varchar(255) NOT NULL

 

Here's where it gets fun.  The categories translate like so:

 

article_ID int(10) PRIMARY KEY AUTO_INCREMENT,  ................................ Will discuss later
articleName varchar(100) NOT NULL ...................................... ArticleName varchar(100) NOT NULL
author varchar(100) NOT NULL ......................................... Author varchar(100) NOT NULL
articleType varchar(30) NOT NULL, .......................... Will discuss later
date date, .................................................... thedate date NULL
body longtext NOT NULL, ..................................... thecontent longtext NULL
grade char(2), ................................................... grade char(2) NOT NULL
source varchar(60), ............................................. source varchar(60) NOT NULL
sourceURL varchar(255) ...................................... link varchar(255) NOT NULL

 

The first area it gets tricky is the article ID.  The table I'm taking data from has an auto_incrementing 'Serial', but also has a 'theid' column which increments, not automatically, but based on data in another table.  I want to do a comparison, so that I ONLY TAKE DATA if 'theid' >= 30000 AND then I want to set the 'theid' value as the new table's 'article_ID'.

 

The second area is the articleType field.  In the table I'm taking data from, there is a 'Category' field which consists of 'News', 'Reviews', 'tier' and a few others.  I need to do a comparison on these, so that, for example, everywhere the 'Category' is 'News' the articleType gets entered as 1, and similar comparisons for the rest.

 

So my question is, how do I write a query that creates AND populates with the data in the way I need?  I am OK with SQL, but this is above my head.  I'm hoping you guys can help guide me in the right direction.

 

[Barand]

It would be something like this

INSERT INTO new_articles (article_ID, articleName, author, articleType, date, body, grade, source, sourceURL)
SELECT theid, articleName, author, 
    CASE Category WHEN 'News' THEN 1 WHEN 'Reviews' THEN 2 ELSE 3 END, 
    theDate, theContent, grade, source, link
FROM articles
WHERE theid >= 30000

 

Note you need a different table name for the new table.