Perform INSERT with SELECT to insert multiple reco

问题:

In the diagram below there is a 1:1 relationship between 'DodgyOldTable' and 'MainTable'. Table 'Option' contains records with 'OptionVal1', 'OptionVal2' and 'OptionVal3' in the 'OptionDesc' field. I need to do an insert into MainTable_Option with a select from DodgyOldTable. Something like this:

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

If possible I want to avoid using several different select statements to perform the insert operation.

alt text http://www.freeimagehosting.net/uploads/863f10bf5f.jpg

回答1:

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

回答2:

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!

回答3:

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

回答4:

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'

回答5:

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.

回答6:

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
...