sql query for exporting only the questions without the answers and comments from the qa_post table in the q2a database?

Question

I am trying to export all the questions from my qa_post table . I do not want to import the answers and comments too.

Scenario:

I am trying to exxport questions from my qa_post table and then import it into a different table.

I will export the answers from the same qa_post table and import them into another table

i will do the same for the comments.

The problem is that q2a database stores the questions,answers and comments in the same qa_post table and give it a type Q, A and C.

Can any body help me with and sql export query that will only export content of type Q from the qa_post table,

i will look at the query and modify it for content of type C and A.

Thank you in advance.

Answer 1

So you want to "copy" questions from the qa_posts table into new_table. As you don't mention the structure of new_table I will have to assume it doesn't exist and you want to create it with the same structure of qa_posts.

 

1. Duplicate the table without content:

 

CREATE TABLE new_table LIKE qa_posts;

 

2. Insert all questions (including hidden and pending approval questions):

 

INSERT new_table SELECT * FROM qa_posts WHERE type LIKE 'Q%';

 

EDIT:

Based on your comment in which you don't want to have the same structure in both tables and you want an ID and a CONTENT in the new table this should do the trick:

 

CREATE TABLE new_table (

    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    content VARCHAR(8000) DEFAULT NULL,

    PRIMARY KEY (id)

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

 

INSERT INTO new_table (id, content)

SELECT postid, content

FROM qa_posts

WHERE `type` LIKE 'Q%';

 

First one creates the table, second one inserts only the required fields. See how the (id, content) matches postid, content. Order is important.

 

Comments

Pupi1985, Thank you very much for your answer. New_table exist with a different structure from qa_post BUT WITH SIMILAR COLUMNS. I intend to restructure both new_table and qa_post to have the same columns and names before applying the queries you gave. Your answer has really given me ideas about how to go about my task. Thank you once again.

---

Hmm... I wouldn't advise you to modify the structure of qa_post table. If you do so, then yo might have trouble when updating to new versions of Q2A. You should try to keeping the way it is and adapt your work to the core, not the other way around. Also if you don't want both tables to have the same structure, the second query would have to be changed because it is assuming the same structure in both tables. Good to know this helped, but as I always say: it makes no sense to say thanks in a question and answer forum as that is what upvoting or accepting answers is there for :)

---

Ok, assuming i don't want both tables to have the same structure, what will the second query be? I am still new at php so my sql knowledge is not that great.  Both new_table and qa_post have an id column and a content column. so if i should maintain the structure of qa_post without any modifications what will be my new query for "copying" only the questions from qa_post to new_table?

---

Now I've covered that scenario in the answer

---

Wow you just saved me from this nightmare. I have implemented your queries and they work like magic. Although upvotes means gratitude i cant help but still say thank you.