Perform INSERT with SELECT to insert multiple records.

Answer 1

I'd say that a manual migration script would be easier to use then trying to do it in a single sql query, if that is an option.

Answer 2

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'

Answer 3

You could UNION all of selects together to give one result set but it depends on your reasons for not wanting the multiple selects - if there are too many or the number of selects may change frequently it'll still be a pain to amend the query with the additional selects. Unfortunatly I think you will have to put the logic somewhere that determines which bit(s) of DodgyOldTable map to the new structure and either write a migration script (or SSIS package) to bulk migrate (if this is a one off job) or UNION your results together...

INSERT MainTable_Option ([MainTableID],[OptionID])
SELECT ID, (CASE WHEN OptionVal1 = 'y' THEN (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal1') END
FROM DodgyOldTable
WHERE OptionVal1 = 'y
UNION
SELECT ID, (CASE WHEN OptionVal2 = 'y' THEN (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal2') END
FROM DodgyOldTable
WHERE OptionVal2 = 'y
...

Answer 4

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')

Answer 5

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!

Answer 6

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