Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2006

    Unanswered: Extracting data from large SINGLE-table database to MULT-table relational database

    Hi All.

    I have a very large single-table database of articles that I want to convert to a multi-table, relational database.

    The existing single-table database contains fields for article author, article source, and article category, where several 'author', 'source', and 'category' IDs repeat dozens of times for hundreds of different articles.

    I want to create seperate tables for author, source, and category and populate the new tables by extracting data from the original single-table database by unique ID field.

    I figured out how to use INSERT and SELECT to pull data in new tables, but can't figure out how to pull only a single instance of a unique author, source, and category to create master reference tables for author/source/category.

    Any assistance would be much appreciated.

    Thanks in advance.


  2. #2
    Join Date
    Oct 2006
    Maybe try this:
    -- This creates the table
    CREATE TABLE author(
    name varchar(50) NOT NULL

    -- This inserts all the existing author, only once
    INSERT INTO author ( name)
    SELECT DISTINCT author_name FROM articles

    -- This creates a column in articles table to hold the recently generated author_id

    ALTER TABLE articles ADD author_id int NULL

    -- Add the author_id in articles table

    UPDATE articles
    INNER JOIN author ON articles.author_name =
    SET articles.author_id = author.author_id

    -- You may want to ensure that all worked well, and do a backup before this last step

    ALTER TABLE articles DROP author_name

    Repeat this steps for source and category as well. Hope this helps!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts