CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Matthew Dresser
Matthew Dresser

Reputation: 11442

Perform INSERT with SELECT to insert multiple records

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

Upvotes: 20

Views: 58085

Answers (7)

kmihingo
kmihingo

Reputation: 384

11 Yrs later but might help someone:

INSERT INTO schema.tableName (col1, col2,col3, col4, col5, col6, col7)
      SELECT col1, 
             col2,
             'Static value',
             CURRENT_TIMESTAMP,
             'Anothe static value', 
             1, 
             (SELECT col7 FROM schema2.anotherTableName)
       FROM schema.tableX;

col1 & col2 come from the same select

col3, col5 are static strings

col4 is the current system time

col6 a Num value,

col7 from a select that returns one value.

NB: Ensure the select with multiple values comes at the beginning. This is MySQL

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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'

Upvotes: 2

Maksym Gontar
Maksym Gontar

Reputation: 22775

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

Upvotes: 2

user70348
user70348

Reputation:

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!

Upvotes: 2

Tomalak
Tomalak

Reputation: 338108

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

Upvotes: 37

Rich Andrews
Rich Andrews

Reputation: 4188

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

Upvotes: 0

thr
thr

Reputation: 19476

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.

Upvotes: 0

Related Questions