基于 ID 列表的 SQL LOOP INSERT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5555841/
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
SQL LOOP INSERT Based on List of ID's
提问by Ayo
Hey I have SQL writers block. So here is what I'm trying to do based on pseudo-code
嘿，我有 SQL 编写器块。所以这就是我试图基于伪代码做的事情
int[] ids = SELECT id FROM (table1) WHERE idType = 1 -> Selecting a bunch of record ids to work with
FOR(int i = 0; i <= ids.Count(); ++i) -> loop through based on number of records retrieved
{
INSERT INTO (table2)[col1,col2,col3] SELECT col1, col2, col3 FROM (table1)
WHERE col1 = ids[i].Value AND idType = 1 -> Inserting into table based on one of the ids in the array
// More inserts based on Array ID's here
}
This is sort of the idea I'm trying to achieve, I understand that arrays are not possible in SQL but I've listed it here to explain my goal.
这是我想要实现的想法，我知道在 SQL 中不可能使用数组，但我在这里列出了它来解释我的目标。
回答by Mikael Eriksson
This is what you are asking for.
这就是你所要求的。
declare @IDList table (ID int)
insert into @IDList
SELECT id
FROM table1
WHERE idType = 1
declare @i int
select @i = min(ID) from @IDList
while @i is not null
begin
INSERT INTO table2(col1,col2,col3)
SELECT col1, col2, col3
FROM table1
WHERE col1 = @i AND idType = 1
select @i = min(ID) from @IDList where ID > @i
end
But if this is all you are going to do in the loop you should really use the answer from Barry instead.
但是，如果这就是您在循环中要做的全部事情，那么您应该真正使用 Barry 的答案。
回答by codingbadger
You can just use:
你可以只使用：
Insert Into Table2 (Col1, Col2, Col3)
Select col1, Col2, Col3
From Table1
Where idType = 1
Why would you even need to loop through each id individually
为什么你甚至需要单独遍历每个 id
回答by Dustin Laine
INSERT INTO table2
(
col1,
col2,
col3
)
SELECT
table1.col1,
table1.col2,
table1.col3
FROM table1
WHERE table1.ID IN (SELECT ID FROM table1 WHERE table1.idType = 1)
