Perform INSERT with SELECT to insert multiple records

perhaps not the most efficient solution but by using a union, this should work.

INSERT MainTable_Option ([MainTableID],[OptionID])
SELECT ID, (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal1')
FROM DodgyOldTable dot
WHERE OptionVal1 = 'y'
UNION SELECT ID, (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal2')
FROM DodgyOldTable dot
WHERE OptionVal2 = 'y'
UNION SELECT ID, (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal3')
FROM DodgyOldTable dot
WHERE OptionVal3 = 'y'

My experience is it is often more easy and more readable to divide it up into smaller bits. So don't try to do everything in one single query. Especially when you are making migration scripts this should not be an issue.

Write down the steps, maybe introduce a temporary table, write the scripts to migrate your data and you are good to go!

What about CROSS JOIN solution?

DECLARE @DodgyOldTable TABLE (ID INT, OptionVal1 CHAR, OptionVal2 CHAR, 
  OptionVal3 CHAR)
INSERT INTO @DodgyOldTable
SELECT 1, 'y', 'n', 'y' UNION
SELECT 2, 'y', 'n', 'n' UNION
SELECT 3, 'n', 'n', 'y' UNION
SELECT 4, 'y', 'y', 'y' UNION
SELECT 5, 'n', 'n', 'n'

DECLARE @Option TABLE (OptionID INT, OptionDesc VARCHAR(100))
INSERT INTO @Option
SELECT 1, 'OptionVal1' UNION
SELECT 2, 'OptionVal2' UNION
SELECT 3, 'OptionVal3'

SELECT ID, OptionID FROM
(
    SELECT 
        ID, 
        CASE    
          WHEN (OptionVal1 = 'y' AND OptionDesc = 'OptionVal1') 
            OR (OptionVal2 = 'y' AND OptionDesc = 'OptionVal2') 
            OR (OptionVal3 = 'y' AND OptionDesc = 'OptionVal3')
          THEN OptionID 
          ELSE NULL 
        END AS OptionID 
    FROM @DodgyOldTable DOT CROSS JOIN @Option O 
)CRS
WHERE OptionID IS NOT NULL

INSERT 
  MainTable_Option 
  (
  MainTableID, 
  OptionID
  )
SELECT
  d.ID, 
  o.OptionId
FROM
  DodgyOldTable d
  INNER JOIN Option o ON
    (d.OptionVal1 = 'Y' AND o.OptionDesc = 'OptionVal1') OR
    (d.OptionVal2 = 'Y' AND o.OptionDesc = 'OptionVal2') OR
    (d.OptionVal3 = 'Y' AND o.OptionDesc = 'OptionVal3')