Multiple sub select statements in an insert into command.

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 30, 2018 at 9:41 am

  #415525

  Hello, 

  I'm having a bit of difficulty getting the following to work.

  
INSERT INTO BANDALLOCATION
(BANDSYSID,ZONEID,RATETYPE,SIMPLEBREAKSINDICATOR,SERVICEID,VEHICLEID)
SELECT
       1161,
       1783,
       'Mileage',
       1,
       (SELECT SERVICEID FROM SERVICE WHERE BANDTYPE = 'Sameday'),
       (SELECT VEHICLEID FROM VEHICLE WHERE SERVICEID IN (SELECT SERVICEID FROM SERVICE WHERE BANDTYPE = 'Sameday'))


  In the above query the first 4 fields (bandsysid, zoneid, ratetype and simplebreaksindicator) are going to be fixed values. My problem is I need to insert multiple rows for a serviceid and multiple rows for a vehicleid where the bandtype is "Sameday".

  Anyone have any idea where I am going wrong with the syntax or is there another way to do it please?

  Thanks in advance
  Paul.

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  August 30, 2018 at 9:59 am

  #2004028

  
INSERT INTO BANDALLOCATION
  (BANDSYSID,ZONEID,RATETYPE,SIMPLEBREAKSINDICATOR,SERVICEID,VEHICLEID)
  SELECT
      1161,
      1783,
      'Mileage',
      1,
      S.SERVICEID,
      V.VEHICLEID
FROM SERVICE S
INNER JOIN VEHICLE V ON V.SERVICEID = S.SERVICEID
WHERE S.BANDTYPE = 'Sameday'

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 30, 2018 at 10:06 am

  #2004032

  Brilliant, thank you very much Scott.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 30, 2018 at 11:02 am

  #2004046

  Edit: Scott beat me to it.

  Does the following get you closer?  You can highlight and run just the SELECT part to ensure that the data looks correct prior to running it as part of the INSERT.
  
INSERT INTO [dbo].[BANDALLOCATION](BANDSYSID,ZONEID,RATETYPE,SIMPLEBREAKSINDICATOR,SERVICEID,VEHICLEID)
SELECT
1161      AS BANDSYSID
, 1783     AS ZONEID
, 'Milage'    AS RATETYPE
, 1      AS SIMPLEBREAKSINDICATOR
, [srv].[SERVICEID] AS SERVICEID
, [veh].[VEHICLEID] AS VEHICLEID
FROM
[dbo].[SERVICE]    AS [srv]
INNER JOIN [dbo].[VEHICLE] AS [veh]
  ON [srv].[SERVICEID] = [veh].[SERVICEID]
WHERE
[srv].[BANDTYPE] = 'Sameday';
GO