Insert multiple rows using subquery

Question

This is the request:

INSERT INTO qualification_lookup (variation, correct_qualification) SELECT (SELECT Qualification FROM student WHERE Qualification like 'A%') ,'A-Level' 

This is the error I get if I try to execute a request.

Msg 512, Level 16, State 1, Line 1 A subquery returns more than 1 value. This is unacceptable when a subquery follows = ,! =, <, <=,>,> = or when the subquery is used as an expression. Application completed.

I am new to SQL so kindly if someone tells me any alternative to this.

Answer 1

 INSERT INTO qualification_lookup (variation, correct_qualification) select Qualification,'A-Level' from student where Qualification like 'A%' 

Answer 2

  INSERT INTO qualification_lookup (variation, correct_qualification) select Qualification, 'A-Level' from student where Qualification like 'A%' 

The correct syntax

Answer 3

The problem is this subquery:

 select Qualification from student where Qualification like 'A%' 

returns multiple rows. This is why you get 512 error message

No need to use this.

That would be enough:

 INSERT INTO qualification_lookup (variation, correct_qualification) select Qualification, 'A-Level' as correct_qualification from student where Qualification like 'A%' 

Answer 4

You need to think about how you build your query - think about what you would get if you just ran this:

  SELECT (select Qualification from student where Qualification like 'A%') ,'A-Level' 

The exact error you get will be my guess - you have a list of many Qualifications trying to match a single line - "A-level".

On the other hand, it will work fine.

  select Qualification, 'A-Level' from student where Qualification like 'A%' 

The trick with the INSERT and UPDATE , in my opinion, is to write a SELECT that will get you what you need, and then wrap it like

 INSERT INTO qualification_lookup (variation, correct_qualification) select Qualification, 'A-Level' from student where Qualification like 'A%'