Insert data into table1 based on a query from table2

Gambit901 0
Viewed 49

[DisneyPassID] and [PersonID] are the same value and will be coming a different table.
[MickeyID],[Comment] and [CafeComment] will be the same for all records.

table1
[MickeyID],[DisneyPassID],[Comment],[CafeComment],[PersonID] ) 
12    ,    1234,          'test',   'sample',      1234)

table2
[goofID],[GoofPassID],[Comment]
23,       2334,       'blue',  `enter code here`
21,       2311,       'test',  
23,       5432,       'yellow',  
23,       1221,       'test',  
23,       5533,       'blue',  
23,       1223,       'blue',  
21,       2334,       'blue',  
21,       2311,       'test',  
23,       5432,       'yellow',  
23,       1221,       'test',  
25,       5533,       'blue',  
23,       1223,       'yellow',

Query for table2. Basically I'm gathering all GoofPassIDs based on Comment uf they are blue or yellow and goofID 23

SELECT GoofPassID
FROM [Space004].[dbo].[table2]
where 
(Comment = 'blue' 
or Comment = 'yellow' 
) 
and goofID = 23

will gives me 2334,5432, 5533..etc..

Desired result would be

    table1
    [MickeyID],[DisneyPassID],[Comment],[CafeComment],[PersonID] ) 
    12    ,    1234,          'test',   'sample',      1234)
    12    ,    2334,          'test',   'sample',      2334)
    12    ,    5432,          'test',   'sample',      5432)

Any ideas on how to write this?

insert select sql-server sql
1 Answers

You can use a CTE and create a custom rank column using an integer to always get table1's results above table2. Alternatively, you can wrap the entire query in an outer query instead of using a CTE (details in my Fiddle below). If you don't care about the table order, you can take the query out of the CTE. I've also utilized a CROSS JOIN and UNION.

WITH CTE AS 
    (SELECT 
        1 AS Rank,
        MickyID,
        DisneyPassID,
        Comment,
        CafeComment,
        PersonID
      FROM table1
         UNION
      SELECT
         2 AS Rank,
         a.MickyID, 
         b.GoofPassID AS DisneyPassID, 
         a.Comment, 
         a.CafeComment, 
         b.GoofPassID AS PersonID
      FROM table1 a
      CROSS JOIN table2 b
      WHERE (b.Comment = 'blue' OR b.Comment = 'yellow') AND b.goofID = 23)
SELECT
  MickyID,
  DisneyPassID,
  Comment,
  CafeComment,
  PersonID
FROM CTE
ORDER BY Rank ASC
MickyID DisneyPassID Comment CafeComment PersonID
12 1234 test sample 1234
12 1223 test sample 1223
12 2334 test sample 2334
12 5432 test sample 5432
12 5533 test sample 5533

See Fiddle.

GRIV0
Related