There may be a technique for that in SQL, but I sure don't know it.
I'd likely approach this problem with an external language like perl.
You'll still end up processing about 15 million inserts, so it'll take a
while, but it shouldn't actually end up being all that rough on your
database server (a simple select and a bunch of simple inserts).
Here's a rough outline of what I'd do (in pseudo-code) ...
while $event = select * from old_event_data #(select as hashref)
foreach my $colnum (1..30)
my $event_id = $event{"event_id"}
my $indexval = ($event{"part"} * 30) + $colnum
my $value = $event{"p$colnum"}
insert into new_event_data ($event_id, $indexval, $value)
end
end
To speed up the inserts a bit, you could borrow a technique used in
bulk-loading data and disable keys on the new table prior to running
this - then re-enable them when it's all done. Might save some time
overall.
Hope this helps.
Dan
Rob Desbois wrote:
Rob, to clarify, your new 'index' column will be based on the value of
the 'part' column and individual column names from the old table?
That is correct.
Perhaps something like this, where [colnum] is derived from column name
like p1? (part+1)*[colnum]
The actual formula I want to use is:
`index` = (30 * part) + [colnum]
The problem is I don't know how to implement this in an SQL statement - what I
want is something like an INSERT...SELECT which can split the SELECTed columns
of a single row (id, c1, c2, c3) into multiple rows in the INSERT: (id, 1, c1),
(id, 2, c2), (id, 3, c3).
Afaik there is no such thing so I need an equivalent method - one that isn't
going to kill my server (like the several attempts I've made so far!)
--Rob
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
