1

I'm using a InnoDB/memcache as a temp holder for rows not processed and placed into it's table with indexes (which slowed down the initial load by a lot and we need to handle spikes). The table is as follows

memcached/innodb: c1|c2|c3|c4

I need to get that data now into two tables such as u1|c1|c2|c3 (u1 is a new unique id generated by auto_inc of that table) u1|c4 (u1 is copied from previous table so it's same)

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Jason K
  • 55
  • 1
  • 7

1 Answers1

1

I'd first insert into the first table, and then query it to insert into the second one:

INSERT INTO t1 (c1, c2, c3) -- u1 is left out, so it can be generated
SELECT c1, c2, c3
FROM   INPUT

INSERT INTO t2 (u1, c4)
SELECT t1.u1, input.c4
FORM   t1
JOIN   t1.c1 = input.c1 AND
       t1.c2 = input.c2 AND
       t1.c3 = input.c3
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • yea that would cause a lot of overhead though. i was hoping mysql had a better way built in otherwise insert_id(); at the application level might be more suiteable – Jason K Aug 14 '16 at 16:22