Professional Web Applications Themes

Merging identical tables - MySQL

I have created a custom Apache logfile entries database using Php and MySQL. It reads the logfiles and inserts each entry into a database. My original idea was to create a new database table every month to keep the table sizes smaller - and ... well ... it just seemed like it would be much more manageable. It appears to me now that I made a mistake, so I would like to now merge four tables into one and I am wondering what the easiest way to do that is. I currently have four tables: jan06 feb06 mar06 april06 They ...

  1. #1

    Default Merging identical tables

    I have created a custom Apache logfile entries database using Php and
    MySQL. It reads the logfiles and inserts each entry into a database.

    My original idea was to create a new database table every month to keep
    the table sizes smaller - and ... well ... it just seemed like it would
    be much more manageable.

    It appears to me now that I made a mistake, so I would like to now merge
    four tables into one and I am wondering what the easiest way to do that is.

    I currently have four tables:
    jan06
    feb06
    mar06
    april06

    They are identical in structure. They have an entry_id as the primary
    key (auto_increment) and then columns to hold the log file data for each
    entry. I think I know how to insert all the records from each table
    into one new table (simply called "logentries"), but I am confused about
    how to handle the auto increment column - the primary key. Each table
    starts at 1 so they use the same values.

    Does MySQL have a built-in procedure for merging these tables? What
    would be the easiest way to copy (or merge) all four of these tables
    into one - with an auto_incremented primary key (entry_id)?

    Thanks in Advance,

    --
    *****************************
    Chuck Anderson • Boulder, CO
    [url]http://www.CycleTourist.com[/url]
    *****************************
    Chuck Anderson Guest

  2. #2

    Default Re: Merging identical tables

    >I have created a custom Apache logfile entries database using Php and
    >MySQL. It reads the logfiles and inserts each entry into a database.
    >
    >My original idea was to create a new database table every month to keep
    >the table sizes smaller - and ... well ... it just seemed like it would
    >be much more manageable.
    Using variable table names is almost always a mistake. It is preferable
    to add a column and put what would have been the table name, or part of
    it, into that column.
    >It appears to me now that I made a mistake, so I would like to now merge
    >four tables into one and I am wondering what the easiest way to do that is.
    >
    >I currently have four tables:
    >jan06
    >feb06
    >mar06
    >april06
    >
    >They are identical in structure. They have an entry_id as the primary
    >key (auto_increment) and then columns to hold the log file data for each
    >entry. I think I know how to insert all the records from each table
    >into one new table (simply called "logentries"), but I am confused about
    >how to handle the auto increment column - the primary key. Each table
    >starts at 1 so they use the same values.
    >
    >Does MySQL have a built-in procedure for merging these tables? What
    >would be the easiest way to copy (or merge) all four of these tables
    >into one - with an auto_incremented primary key (entry_id)?
    Something like:

    INSERT INTO bigtable(entry_id, whole, bunch, of, other, fields)
    SELECT null, whole, bunch, of, other, fields from jan06 ;

    Note that this just throws out the existing entry_id and creates
    a new one.

    Some issues: If you've got four tables each with an entry_id and
    it's referenced elsewhere so you can't just throw away the existing
    entry_id and generate a new one, you've got a problem. You might
    try adding 10000 to all the IDs from jan06, 20000 from all the IDs
    from feb06, etc. so the numbers don't overlap. You still have
    to deal with fixing the references the same way.

    INSERT INTO bigtable(entry_id, whole, bunch, of, other, fields)
    SELECT entry_id + 10000, whole, bunch, of, other, fields from jan06 ;
    INSERT INTO bigtable(entry_id, whole, bunch, of, other, fields)
    SELECT entry_id + 20000, whole, bunch, of, other, fields from feb06 ;

    I presume there's a date field in there somewhere so you can still
    tell what month the row belongs to.

    Gordon L. Burditt
    Gordon Burditt Guest

  3. #3

    Default Re: Merging identical tables

    Gordon Burditt wrote:
    >>I have created a custom Apache logfile entries database using Php and
    >>MySQL. It reads the logfiles and inserts each entry into a database.
    >>
    >>My original idea was to create a new database table every month to keep
    >>the table sizes smaller - and ... well ... it just seemed like it would
    >>be much more manageable.
    >>
    >>
    >
    >Using variable table names is almost always a mistake. It is preferable
    >to add a column and put what would have been the table name, or part of
    >it, into that column.
    >
    >
    >
    >>It appears to me now that I made a mistake, so I would like to now merge
    >>four tables into one and I am wondering what the easiest way to do that is.
    >>
    >>I currently have four tables:
    >>jan06
    >>feb06
    >>mar06
    >>april06
    >>
    >>They are identical in structure. They have an entry_id as the primary
    >>key (auto_increment) and then columns to hold the log file data for each
    >>entry. I think I know how to insert all the records from each table
    >>into one new table (simply called "logentries"), but I am confused about
    >>how to handle the auto increment column - the primary key. Each table
    >>starts at 1 so they use the same values.
    >>
    >>Does MySQL have a built-in procedure for merging these tables? What
    >>would be the easiest way to copy (or merge) all four of these tables
    >>into one - with an auto_incremented primary key (entry_id)?
    >>
    >>
    >
    >Something like:
    >
    > INSERT INTO bigtable(entry_id, whole, bunch, of, other, fields)
    > SELECT null, whole, bunch, of, other, fields from jan06 ;
    >
    >Note that this just throws out the existing entry_id and creates
    >a new one.
    >
    >
    Exactly what I want to do. I can create this INSERT/SELECT using
    PhpMyAdmin. In fact ... I just did. Very nice. Thanks.
    >Some issues: If you've got four tables each with an entry_id and
    >it's referenced elsewhere so you can't just throw away the existing
    >entry_id and generate a new one, you've got a problem.
    >
    Thankfully I don't have that problem, but thanks for going into this detail.
    >I presume there's a date field in there somewhere so you can still
    >tell what month the row belongs to.
    >
    >
    Yes, I do. There's a datetime field for the date and time of the http
    request.

    Thanks for the help.

    --
    *****************************
    Chuck Anderson Boulder, CO
    [url]http://www.CycleTourist.com[/url]
    *****************************
    Chuck Anderson Guest

Similar Threads

  1. How identical is a disk mirror?
    By Eddie in forum Linux / Unix Administration
    Replies: 2
    Last Post: October 14th, 08:53 PM
  2. Replies: 0
    Last Post: August 22nd, 09:41 AM
  3. Replacing identical lines in a RE
    By Gunter Schelfhout in forum PERL Miscellaneous
    Replies: 6
    Last Post: August 13th, 11:17 AM
  4. Creating tables and merging them for multiple data
    By Agin in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 03:39 PM
  5. Merging 200 tables
    By Tom S. in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 06:44 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139