6

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?

Anders
  • 8,307
  • 9
  • 56
  • 88
Giannis Grivas
  • 3,374
  • 1
  • 18
  • 38

1 Answers1

8

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'
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Ok . Let me try it a bit. – Giannis Grivas Nov 14 '15 at 18:07
  • I will use this script as migration on production databases. Is this the best solution for me? – Giannis Grivas Nov 14 '15 at 18:10
  • @GiannisGrivas: That's the best I can think of, in particular the `CROSS JOIN` variant... – Lukas Eder Nov 14 '15 at 18:17
  • What about performance? http://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union shows that UNION is faster, isn't it? – Giannis Grivas Nov 14 '15 at 18:21
  • @GiannisGrivas ;) Don't make such general assumptions, just because this was what's printed out on a trivial execution of similar queries. In this case, I believe that `CROSS JOIN` will outperform `UNION ALL`. Measure it yourself against your real-world data set, though. – Lukas Eder Nov 14 '15 at 18:26
  • 2
    @GiannisGrivas . . . I see nothing in that question that suggests that one is faster than the other. In this case, I would expect the `cross join` to have better performance on large result sets, but you really wouldn't see the difference unless the `ServiceItems` table did not fit in memory. – Gordon Linoff Nov 14 '15 at 18:37