sql - 如何将 SELECT 查询的每个结果插入三个新行到同一个表中

Question

我正在使用 SQL Server。对于每个:

select * from ServiceItems where Itemtypeid=7004 (query1)

我想在同一个表中插入三个新行，例如:

(ItemID, PackageID, ItemTypeID, ServiceID, ItemName, CreatedDate) VALUES
(19377, 5352, 7007, 2011, N'L1', '11/11/2015 6:50:51 PM'), 
(19378, 5352, 7008, 2011, N'M1', '11/11/2015 6:50:51 PM'), 
(19376, 5352, 7006, 2011, N'W1', '11/11/2015 6:50:51 PM') 

ItemID = is the primary key
PackageID = one from query1
ItemTypeID = as it is 7006,7007,700
ServiceID = one from query1
ItemName =  as it is L1,M1,W1
CreatedDate = time now

我尝试INSERT INTO SELECT...

INSERT INTO ServiceItems (PackageID, ItemTypeID, ServiceID, ItemName, CreatedDate)
SELECT PackageID, '7006', ServiceID, 'W1','' FROM ServiceItems WHERE ItemID = '7004'

但是这个会添加一行。我必须创建三个单独的查询吗？使用光标怎么样？

Answer 1

您可以使用UNION ALL:

INSERT INTO ServiceItems (PackageID, ItemTypeID, ServiceID, ItemName, CreatedDate)

SELECT PackageID, '7006', ServiceID, 'W1', current_timestamp 
FROM ServiceItems 
WHERE ItemID = '7004'

UNION ALL

SELECT PackageID, '7007', ServiceID, 'L1', current_timestamp 
FROM ServiceItems 
WHERE ItemID = '7004'

UNION ALL

SELECT PackageID, '7008', ServiceID, 'M1', current_timestamp 
FROM ServiceItems 
WHERE ItemID = '7004'

或者更好的是CROSS JOIN:

INSERT INTO ServiceItems (PackageID, ItemTypeID, ServiceID, ItemName, CreatedDate)
SELECT s.PackageID, x.ItemTypeId, s.ServiceID, x.ItemName, current_timestamp
FROM ServiceItems AS s 
CROSS JOIN (
  VALUES ('7006', 'W1'), 
         ('7007', 'L1'), 
         ('7008', 'M1')
) AS x (ItemTypeId, ItemName)
WHERE s.ItemID = '7004'