Msg 120, Level 15, State 1, Procedure Generate_Exame, Line 6,The select list for the INSERT statement contains fewer items than the insert list

I want insert in question table that has these columns

C#_T_F_Id, C#_T_F_Q, C#_T_F_Choices, C#_Mcq_Id, C#_MCQ_Q, C#_Choices 

After execute Generate_Exame procedure what should I do :

create procedure Generate_Exame      @course_id int as      if @course_id = 600     begin         insert into [dbo].[Question](C#_T_F_Id, C#_T_F_Q, C#_T_F_Choices,                                      C#_Mcq_Id, C#_MCQ_Q, C#_Choices)             select *              from                 (select top(3)                       T.C#_T_F_Id, T.C#_T_F_Q, T.C#_T_F_Choices                  from                       C#_T_F T                  order by                      newid()) as t1             union all             select *              from                 (select top(7)                       C.C#_Mcq_Id C#_Q_id, C.C#_MCQ_Q C#_question, C.C#_Choices Choices                                from                       C#_MCQ C                  order by                      newid()) as t2)     end 
Add Comment
2 Answer(s)

If I understand well you want to:

  • Insert data into a table from a combined result set.
  • Combine two result sets side by side. The first one provides columns 1, 2, and 3, while the second one provides column 4, 5, and 6.
  • On top of this both result sets (left and right) do not have the same lenght. One has 3 rows, while the other has 7 rows. I assume these numbers may vary.
  • There’s no set order for the rows on the left, or the rows on the right. You are producing them by ordering using a random UUID, so that can change every time you run the query.

In order to do this you need to produce a row number on each side. Then a simple full join will combine both result sets.

For example:

insert into [dbo].[Question] (   C#_T_F_Id, C#_T_F_Q, C#_T_F_Choices,   C#_Mcq_Id, C#_MCQ_Q, C#_Choices ) select -- Step #4: produce combined rows, ready for insert   a.T.C#_T_F_Id, a.T.C#_T_F_Q, a.T.C#_T_F_Choices,   b.C#_Q_id, b.C#_question, b.Choices from ( -- Step #1: Produce the left result set with row number (rn)   select *, row_number() over(order by ord) as rn   from (     select top(3)       T.C#_T_F_Id, T.C#_T_F_Q, T.C#_T_F_Choices,        newid() as ord     from C#_T_F T      order by ord   ) x ) a  full join ( -- Step #2: Produce the right result set with row number (rn)   select *, row_number() over(order by ord) as rn   from (     select top(7)        C.C#_Mcq_Id C#_Q_id, C.C#_MCQ_Q C#_question, C.C#_Choices Choices,       newid() as ord     from C#_MCQ C     order by ord   ) y ) b on a.rn = b.rn -- Step #3: Full join both result sets by row number (rn) 
Answered on August 30, 2020.
Add Comment

You are having six columns in the INSERT clause. But, you have only 3 columns coming out of the UNION query.

-- You are inserting 6 columns insert into [dbo].[Question](C#_T_F_Id, C#_T_F_Q, C#_T_F_Choices,                                      C#_Mcq_Id, C#_MCQ_Q, C#_Choices)  -- You are selecting only 3 columns.             select *              from                 (select top(3)                       T.C#_T_F_Id, T.C#_T_F_Q, T.C#_T_F_Choices                  from                       C#_T_F T                  order by                      newid()) as t1             union all             select *              from                 (select top(7)                       C.C#_Mcq_Id C#_Q_id, C.C#_MCQ_Q C#_question, C.C#_Choices Choices                                from                       C#_MCQ C                  order by                      newid()) as t2)  

If you need to have 6 columns, you need to join the two SELECT statements in some way, based on JOIN condition.

Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.