Insert Into table from multiple select queries

To select random questions from each domain and insert them into the tblTestDetailSSM table, you can use a combination of Common Table Expressions (CTEs) or derived tables to gather a random question from each domain first, and then perform the final insert operation.

Here is an example of how you might achieve this:

BEGIN
    -- Select random questions from each domain
    WITH RandomMathQuestions AS (
        SELECT TOP 1 ti.IdIntrebare
        FROM tblIntrebare ti
        JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura
        WHERE tp.Complexitate = 'GENERAL' AND tp.Specialitate = 'Math'
        ORDER BY NEWID()
    ),
    RandomPhysicsQuestions AS (
        SELECT TOP 1 ti.IdIntrebare
        FROM tblIntrebare ti
        JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura
        WHERE tp.Complexitate = 'GENERAL' AND tp.Specialitate = 'Physics'
        ORDER BY NEWID()
    ),
    RandomChemistryQuestions AS (
        SELECT TOP 1 ti.IdIntrebare
        FROM tblIntrebare ti
        JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura
        WHERE tp.Complexitate = 'GENERAL' AND tp.Specialitate = 'Chemistry'
        ORDER BY NEWID()
    ),
    -- Repeat for other domains as needed
    RandomOtherQuestions AS (
        SELECT TOP 1 ti.IdIntrebare
        FROM tblIntrebare ti
        JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura
        WHERE tp.Complexitate = 'GENERAL' AND tp.Specialitate = 'Other'
        ORDER BY NEWID()
    )

    -- Final selection combining all random questions
    SELECT IdIntrebare, @IdUserTest AS IdUserTest
    INTO #TempQuestions
    FROM (
        SELECT IdIntrebare FROM RandomMathQuestions
        UNION ALL
        SELECT IdIntrebare FROM RandomPhysicsQuestions
        UNION ALL
        SELECT IdIntrebare FROM RandomChemistryQuestions
        UNION ALL
        SELECT IdIntrebare FROM RandomOtherQuestions
    ) AS CombinedQuestions

    -- Insert into tblTestDetailSSM
    INSERT INTO tblTestDetailSSM (IdIntrebare, IdUserTest)
    SELECT IdIntrebare, IdUserTest
    FROM #TempQuestions

    DROP TABLE #TempQuestions
END

Explanation:

1. CTEs: Each CTE (RandomMathQuestions, RandomPhysicsQuestions, RandomChemistryQuestions, etc.) selects a random question from each specified domain using ORDER BY NEWID().
2. Combining Results: A UNION ALL operation combines the results from all CTEs.
3. Temporary Table: Insert the combined results into a temporary table #TempQuestions to facilitate the final insert.
4. Final Insert: Insert the selected questions into tblTestDetailSSM.

This approach ensures that you get one random question from each specified domain and insert them into the target table. If you need more questions per domain, adjust the TOP clause accordingly.