How to insert multiple rows for n valueitems

Question

Hello everyone

We have a table that is used for assessment-values in our tool where each value has its valueid. Dont ask me who came up with this so called design, but now I need to add multiple valueids to the end for each assessment.

So, it looks like this:

AssessmentId 1, ValueId 1, Value
AssessmentId 1, ValueId 2, Value
AssessmentId 1, ValueId 3, Value
AssessmentId 2, ValueId 1, Value
AssessmentId 2, ValueId 2, Value
AssessmentId 2, ValueId 3, Value

I now probably need a subquery that returns all distinct assessmentids and for each of those I add my rows

AssessmentId 1, ValueId 7, empty
AssessmentId 1, ValueId 8, empty
AssessmentId 1, ValueId 9, empty
AssessmentId 2, ValueId 7, empty
AssessmentId 2, ValueId 8, empty
AssessmentId 2, ValueId 9, empty
...

Honestly, I have no clue how to achieve this. Maybe a subselect with a group, but how would I then get the selected assessmentid into the new insert?

Edit: well, actually we have 709 values for each assessment and now we need 24 more at the end for additional data

foreach distinct assessmentid in table
{ 
   insert row (assessmentid, 710, null); 
   insert row (assessmentid, 711, null); 
   ... 
   insert row (assessmentid, 733, null); 
}

something like this

Answer 1

INSERT
INTO    i_should_really_name_the_tables_i_ask_about_on_stackoverflow_com (assessmentid, valueid)
SELECT  assessmentId, valueMax + lvl
FROM    (
        SELECT  assessmentId, MAX(valueId) AS valueMax
        FROM    i_should_really_name_the_tables_i_ask_about_on_stackoverflow_com t
        GROUP BY
                assessmentId
        ) a
CROSS JOIN
        (
        SELECT  level AS lvl
        FROM    dual
        CONNECT BY
                level <= 24
        ) l