Repeated Insert copies on ID

Question

We have records with a count field on an unique id.

The columns are:

mainId = unique
mainIdCount = 1320 (this 'views' field gets a + 1 when the page is visited)

How can you insert all these mainIdCount's as seperate records in another table IN ANOTHER DBASE in one query?

Yes, I do mean 1320 times an insert with the same mainId! :-) We actually have records that go over 10,000 times an id. It just has to be like this.

This is a weird one, but we do need the copies of all these (just) counts like this.

score 0 · Answer 1 · edited May 23 '17 at 12:05

The most straightforward way to this is with a JOIN operation between your table, and another row source that provides a set of integers. We'd match each row from our original table to as many rows from the set of integer as needed to satisfy the desired result.

As a brief example of the pattern:

 INSERT INTO newtable (mainId,n) 
 SELECT t.mainId 
      , r.n
   FROM mytable t
   JOIN ( SELECT 1 AS n
          UNION ALL SELECT 2
          UNION ALL SELECT 3
          UNION ALL SELECT 4
          UNION ALL SELECT 5
        ) r
  WHERE r.n <= t.mainIdCount

If mytable contains row mainId=5 mainIdCount=4, we'd get back rows (5,1),(5,2),(5,3),(5,4)

Obviously, the rowsource r needs to be of sufficient size. The inline view I've demonstrated here would return a maximum of five rows. For larger sets, it would be beneficial to use a table rather than an inline view.

This leads to the followup question, "How do I generate a set of integers in MySQL",

e.g. Generating a range of numbers in MySQL

And getting that done is a bit tedious. We're looking forward to an eventual feature in MySQL that will make it much easier to return a bounded set of integer values; until then, having a pre-populated table is the most efficient approach.

Repeated Insert copies on ID

1 Answers1