SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with merging tables

    I have a rather shabby database set-up, where content for different areas is contained in different tables. i.e. art content is in the table art_news, programming news is in the table programming_news

    What I want to do is merge all these tables into one big table. The content will still be linked to its section by a section_id field, so that's not an issue.

    The problem I'm having is that the ID fields are clashing when I use
    Code:
    create table content_table as select * from design_news,art_news
    So obviously I cant do it that way. Any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    instead of CREATE TABLE AS SELECT, create the table ahead of time with its own, new, autonumber pk

    CREATE TABLE content_table
    ( newpk int autonumber
    , oldpk int
    , otherfields ...

    then insert the rows from the other tables

    if any tables had foreign keys to the design_news or art_news tables, you'll want to update them

    finally, drop the oldpk column


    rudy

  3. #3
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's quite a nifty way of doing it - I'll try that, thanks

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    You can also try the select into format

    Code:
    SELECT field1, field2, field3, field4
      INTO NewTableName
      FROM OldTable1, OldTable2
     WHERE ...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  5. #5
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow - that looks even better.

    I've been stalling on this to see if I could find a better way of doing it and it turns out that each suggestion is better than the last!

    Thanks


Bookmarks

Posting Permissions

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