Support » Fixing WordPress » mysql statements to add many Posts into one categorie (getting primary key error

  • Hi all,

    could anyone tell me the mysql-statements in order to bulk add many posts?

    AFAK I have to use the table wp_posts and the wp_term_relationship. My catgorie has the ID 75. So I have to add the posts from a csv-File into wp_posts and afterwords I have to add as many records as I put into wp_posts into the table wp_term_relationships with the respective ID = 75 and object_id = all new IDs in wp_terms.

    Does anyone know, how to do that? Below you can see my sql scxript, which is a bit more difficult and results in primary key errors

    Regards and thanks,

    CJC

    USE w;
    
    DROP TABLE IF EXISTS tblPosts;
    CREATE TABLE tblPosts
    (
    pid INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    csv_post_title VARCHAR(255),
    csv_post_post TEXT,
    csv_post_type VARCHAR(255),
    csv_post_excerpt VARCHAR(255),
    csv_post_categorie INT DEFAULT 75,
    csv_post_tags VARCHAR(255),
    csv_post_date VARCHAR(255),
    csv_post_author INT DEFAULT 2
    );
    
    LOAD DATA LOCAL
            INFILE '/home/xyz/fotos/import.csv'
            REPLACE
            INTO TABLE tblPosts
            FIELDS
                    TERMINATED BY ','
                    OPTIONALLY ENCLOSED BY '"'
    (
       csv_post_title,
       csv_post_post,
       csv_post_type,
       csv_post_excerpt,
       csv_post_categorie,
       csv_post_tags,
       csv_post_date,
       csv_post_author
    );
    
    INSERT INTO wp_posts
    (
       wp_posts.post_title,
       wp_posts.post_content,
       wp_posts.post_type,
       wp_posts.post_excerpt,
       wp_posts.post_date,
       wp_posts.post_author
    )
    SELECT
       csv_post_title,
       csv_post_post,
       csv_post_type,
       csv_post_excerpt,
       csv_post_date,
       csv_post_author
    FROM tblPosts
    ;
    
    ALTER TABLE wp_term_relationships ADD COLUMN post_title TEXT;
    
    DELETE FROM wp_posts WHERE ID ANY (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id = 75);
    
    DELETE FROM wp_term_relationships wp_term_relationships WHERE term_taxonomy_id = 75;
    
    INSERT INTO wp_term_relationships
    (
       wp_term_relationships.object_id,
       wp_term_relationships.post_title,
       wp_term_relationships.term_taxonomy_id
    )
    SELECT
       wp_posts.ID,
       tblPosts.csv_post_title,
       75
    FROM tblPosts
    INNER JOIN wp_posts
    ON wp_posts.post_title = tblPosts.csv_post_title
    ;
  • The topic ‘mysql statements to add many Posts into one categorie (getting primary key error’ is closed to new replies.