CODEWITHSUNDEEP
sqlsql-serversql-insert
Giannis Grivas
Giannis Grivas

Reputation: 3412

How to insert three new rows for every result of a SELECT query into the same table

I am using SQL Server. For every:

select * from ServiceItems where Itemtypeid=7004 (query1)

I want to insert into the same table three new rows like:

(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

I tried INSERT INTO SELECT... 

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

but this one will add one row. Do I have to create three separate queries? How about using a cursor?

Upvotes: 6

Views: 322

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

You can use 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'

Or better, a 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'

Upvotes: 8

Related Questions