SQL 使用子查询插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13830312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert multiple rows using subquery
提问by Umar Iqbal
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.
消息 512，级别 16，状态 1，第 1 行子查询返回了 1 个以上的值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。该语句已终止。
I am new to SQL so kindly if someone tells me any alternative to do that.
如果有人告诉我任何替代方法，我对 SQL 很陌生。
回答by Kaf
INSERT INTO qualification_lookup (variation, correct_qualification)
select Qualification,'A-Level' from student where Qualification like 'A%'
回答by jenson-button-event
INSERT INTO qualification_lookup (variation, correct_qualification)
select Qualification, 'A-Level' from student where Qualification like 'A%'
Is the correct syntax
是正确的语法
回答by Dale M
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'.
你得到的确切错误是我的猜测 - 你有许多资格的列表试图与单个字符串匹配 - 'A-level'。
On the other hand, this will work fine
另一方面，这将正常工作
select Qualification, 'A-Level' from student where Qualification like 'A%'
The trick with INSERTand UPDATEstatements, to my mind is to write a SELECTstatement that gets you what you need and then wrap that up like
INSERT和UPDATE语句的诀窍，在我看来是写一个SELECT语句，让你得到你需要的东西，然后像
INSERT INTO qualification_lookup (variation, correct_qualification) select Qualification, 'A-Level' from student where Qualification like 'A%'
回答by Nathan
The problem is this subquery:
问题是这个子查询：
select Qualification from student where Qualification like 'A%'
returns several rows. That's why you get error message 512
返回几行。这就是您收到错误消息 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%'
回答by Gustavo Lage
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
