insert multiple rows using subquery

Question

This is the query:

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 the query.

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

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

Indi · Accepted Answer · 2012-12-11 23:37:48Z

29

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

answered Dec 11, 2012 at 23:37

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jenson-button-event · Accepted Answer · 2012-12-11 23:37:37Z

3

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

Is the correct syntax

answered Dec 11, 2012 at 23:37

jenson-button-event

19k13 gold badges98 silver badges165 bronze badges

Comments

Nathan · Accepted Answer · 2012-12-11 23:41:59Z

3

The problem is this subquery:

select Qualification from student where Qualification like 'A%'

returns several rows. That's why you get error message 512

No need to use that.

This would be enough:

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

answered Dec 11, 2012 at 23:41

Nathan

2,77524 silver badges28 bronze badges

Comments

Dale M · Accepted Answer · 2012-12-11 23:42:44Z

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

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

the exact error you are getting would be my guess - you have a list of many Qualifications trying to be matched with a single string - 'A-level'.

On the other hand, this will work fine

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

The trick with INSERT and UPDATE statements, to my mind is to write a SELECT statement that gets you what you need and then wrap that up like

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

Gustavo Lage · Accepted Answer · 2018-08-06 14:36:27Z

declare @count int
declare @index int = 1
declare @currentQualificationId int = null

set @count = (select distinct count(QualificationId) from Qualification  from  where 
Qualification like 'A%')

set @currentQualificationId = (SELECT QualificationId from (select QualificationId , 
ROW_NUMBER() OVER (ORDER BY QualificationId) AS RowNumber from Qualification where 
Qualification like 'A%') as Qualification
where Qualification.RowNumber = @index) 


while (@index <= @count)
 begin

    INSERT INTO qualification_lookup (variation, correct_qualification) 
     values((select Qualification where QualificationId = 
     @currentQualificationId),'A-Level')

    set @index = @index + 1

     set @currentQualificationId = (SELECT QualificationId from (select 
     QualificationId , ROW_NUMBER() OVER (ORDER BY QualificationId) AS RowNumber from 
     Qualification where Qualification like 'A%') as Qualification
     where Qualification.RowNumber = @index) 
END

Collectives™ on Stack Overflow

insert multiple rows using subquery

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related