August 30, 2018 at 9:41 am
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.
August 30, 2018 at 9:59 am
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 30, 2018 at 10:06 am
Brilliant, thank you very much Scott.
August 30, 2018 at 11:02 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply