sql 在一个表上多次插入，同时循环/迭代另一个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3142993/
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 multiple insert on one table, while looping/iterating over another table?
提问by VoodooChild
I have two table "TempStore" and "Store" with the same column called "Items".
我有两个表“TempStore”和“Store”，同一列名为“Items”。
There is data in "TempStore" table which I need to move over to "Store" table which requires few modifications.
“TempStore”表中有数据，我需要将其移到“Store”表中，该表几乎不需要修改。
I need to iterate over "TempStore" data (i.e. items) and insert into Store...
我需要遍历“TempStore”数据（即项目）并插入到商店...
More specifically: How can I iterate over TempStore (in sql) where "for each item in 'TempStore' I need to store 2 or maybe 3 items in 'Store' with little modification", how can I accomplish this?
更具体地说：我如何迭代 TempStore（在 sql 中），其中“对于 'TempStore' 中的每个项目，我需要在 'Store' 中存储 2 或 3 个项目，而无需修改”，我该如何实现？
What I want to do is take each rowdata from "[SELECT * FROM TempStore]" and insert three records in "Store" with being able to change "items"
我想要做的是从“[SELECT * FROM TempStore]”中获取每个行数据并在“Store”中插入三个记录，并能够更改“items”
回答by KM.
try INSERT-SELECT:
尝试插入选择：
INSERT INTO Store
(col1, col2, col3...)
SELECT
col1, col2, col3...
FROM TempStore
WHERE ...
just make the SELECT return one row for every insert, and produce the values in the Cols that you need. You might need CASEand a join to another table to make the extra rows.
只需让 SELECT 为每个插入返回一行，并在您需要的列中生成值。您可能需要CASE连接到另一个表来生成额外的行。
EDITbased on comments, OP wanted to see the numbers table in action
根据评论进行编辑，OP 希望查看数字表的实际效果
Lets say TempStore table has {Items, Cost, Price, ActualCost, ActualPrice} But in the Store table I need to store {Items, Cost, Price}. The ActualCost and ActualPrice from TempStore datarow would need to be added as another row in Store....(I hope this makes sense)....Anyways, is the solution using "WHILE-BEGIN-END"??
假设 TempStore 表有 {Items, Cost, Price, ActualCost, ActualPrice} 但在 Store 表中我需要存储 {Items, Cost, Price}。来自 TempStore 数据行的 ActualCost 和 ActualPrice 需要作为 Store 中的另一行添加......（我希望这是有道理的）......无论如何，解决方案是否使用“WHILE-BEGIN-END”？？
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY)
INSERT INTO Numbers VALUES(1)
INSERT INTO Numbers VALUES(2)
INSERT INTO Numbers VALUES(3)
INSERT INTO Store
(Items, Cost, Price)
SELECT
t.Items, t.Cost
,CASE
WHEN n.Number=1 THEN t.Price
WHEN n.Number=2 THEN t.ActualCost
ELSE t.ActualPrice
END
FROM TempStore t
INNER JOIN Numbers N ON n.Number<=3
WHERE ...
you could even use a UNION:
你甚至可以使用联合：
INSERT INTO Store
(Items, Cost, Price)
SELECT
t.Items, t.Cost, t.Price
FROM TempStore t
UNION ALL
SELECT
t.Items, t.Cost, t.ActualCost
FROM TempStore t
UNION ALL
SELECT
t.Items, t.Cost, t.ActualPrice
FROM TempStore t
either the Numbers table or the UNION will we WAYbetter than a loop!
无论是数字表或联盟将我们WAY比一个循环！
回答by DOK
OK, I think KM has proposed an excellent solution involving a "numbers table". However, VoodooChild has requested in a comment that I post example code for my suggestion of using WHILE-BEGIN-END around an INSERT-SELECT.
好的，我认为 KM 提出了一个涉及“数字表”的出色解决方案。但是，VoodooChild 在评论中要求我发布示例代码，以便我建议在 INSERT-SELECT 周围使用 WHILE-BEGIN-END。
I have created two tables like VoodooChild's Store and TempStore.
我创建了两个表，如 VoodooChild's Store 和 TempStore。
Store has columns StoreID, StoreName, StoreState, StoreNumber.
商店有列 StoreID、StoreName、StoreState、StoreNumber。
TempStore has columns TempStoreID, TempStoreName.
TempStore 有 TempStoreID、TempStoreName 列。
I prepopulated TempStoreName with the values First, Second, Third and Fourth.
我用值 First、Second、Third 和 Four 预填充了 TempStoreName。
Now, my SQL will insert three records into the Store table for every record in the TempStore table that meets the condition in the WHERE clause. That condition is the length of the TempStoreName, obviously not a real-world example.
现在，我的 SQL 将为 TempStore 表中满足 WHERE 子句中条件的每条记录插入三条记录到 Store 表中。该条件是 TempStoreName 的长度，显然不是真实世界的例子。
DECLARE @counter int
SET @counter = 0;
WHILE @counter < 3
BEGIN
INSERT INTO Store (StoreName, StoreState, StoreNumber)
SELECT TempStoreName, 'AZ', @counter FROM TempStore WHERE LEN(TempStoreName) = 5
SET @counter = @counter + 1
END
The result of this when applied to an empty Store table is:
应用于空 Store 表时的结果是：
StoreID StoreName StoreState StoreNumber
1 First AZ 0
2 First AZ 1
3 First AZ 2
4 Third AZ 0
5 Third AZ 1
6 Third AZ 2
So, this approach works. It appears to meet VoodooChild's needs. It may or may not be the very best choice, but there are other factors involved in the decision that we don't know, such as how many times this operation is going to be repeated.
所以，这种方法有效。它似乎满足了 VoodooChild 的需求。它可能是也可能不是最好的选择，但还有其他我们不知道的决策涉及的因素，例如此操作将重复多少次。
回答by Tom H
Given your latest comment, this should give you what you need. You should probably have some way of differentiating the values in your Stores table once they get there. Perhaps an "actual" BIT column or something similar:
鉴于您的最新评论，这应该可以满足您的需求。一旦它们到达那里，您可能应该有某种方法来区分 Stores 表中的值。也许是一个“实际”的 BIT 列或类似的东西：
INSERT INTO Stores (item, cost, price, actual)
SELECT item, cost, price, 0
FROM TempStores
UNION ALL
SELECT item, actual_cost, actual_price, 1
FROM TempStores
If you needed to adjust the columns (for example, increase actual_price by 10%) then you could do this:
如果您需要调整列（例如，将 actual_price 增加 10%），那么您可以这样做：
INSERT INTO Stores (item, cost, price, actual)
SELECT item, cost, price, 0
FROM TempStores
UNION ALL
SELECT item, actual_cost, 1.1 * actual_price, 1
FROM TempStores
WHERE actual_cost IS NOT NULL
I also added a WHERE clause to the second SELECT statement to show that you can filter the rows. That WHERE clause will only affect the second SELECT. So, you could also do this:
我还在第二个 SELECT 语句中添加了一个 WHERE 子句，以表明您可以过滤行。WHERE 子句只会影响第二个 SELECT。所以，你也可以这样做：
INSERT INTO Stores (item, cost, price, actual)
SELECT item, cost, price, 0
FROM TempStores
WHERE cost IS NOT NULL
UNION ALL
SELECT item, actual_cost, 1.1 * actual_price, 1
FROM TempStores
WHERE actual_cost IS NOT NULL
回答by Alex W
INSERT INTO Store ( SELECT * FROM TempStore UNION ALL SELECT * FROM TempStore )
The above statement will insert two rows in the store for each row in the TempStore. You can change the SELECT * to whatever modification that you want to do to the item.
上面的语句会为 TempStore 中的每一行在 store 中插入两行。您可以将 SELECT * 更改为您想要对项目进行的任何修改。
