INSERT INTO a table using a subquery

Question

I'm working a query that I've never done and I'm stuck on how to proceed. I would like to be a single insert into command.

The pseudo code what I'm trying to do is like this:

For each player 
    insert into CurrentHand table by getting x number of cards needed 
       so all the players have 10 cards in their hands.

So if player 1 will get 7 new cards if he has 3 cards in their hands. Play 2 will get 5 new cards if he has 5 cards in their hands.

So far I've gotten to this select statement but it feels like I'm using the wrong approach.

DECLARE @MaxHandCount int
SET @MaxHandCount = 10

SELECT Player.PlayerId
    , (SELECT COUNT(1) FROM CurrentHand WHERE PlayerId = Player.PlayerId AND IsUsed = 0) AS CurrentHandCount
    , (@MaxHandCount - (SELECT COUNT(1) FROM CurrentHand WHERE PlayerId = Player.PlayerId AND IsUsed = 0)) AS NeededHandCount
    , CardId
FROM Player, AvailableCard
WHERE Cardid IN (SELECT CardId FROM CurrentHand WHERE IsUsed = 0)
ORDER BY PlayerId

The table structure looks like:

Player
  - PlayerId

AvailableCard
  - CardId
  - CardValue

CurrentHand
  - PlayerId
  - CardId
  - IsUsed

Thanks so much.

Answer 1

This was very interesting. Here is my solution to "deal" the needed cards. Please read the commends in the code. This only does the select but I believe you can figure out the insert yourself. Check out the fiddle too.

-- for each card in player's hand assign a sequence number
with cte_currenthand as
(
  select PlayerId, 
    rank() over(partition by PlayerId order by CardId) CardSeq
  from CurrentHand
  where IsUsed = 0
)
-- for each player generate a sequence 1..10
, cte_maxhand as
(
select p.PlayerId, x.seq
from Player p
cross join (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) x(seq)
)
-- get cards the players need and assign them new sequence numbers
-- basically cte_maxhand minus cte_currenthand
, cte_needed as
(
  select mh.PlayerId, row_number() over(order by mh.seq) seq
  from cte_maxhand mh
  left join cte_currenthand ch 
    on ch.CardSeq = mh.seq
    and ch.PlayerId = mh.PlayerId
  where ch.CardSeq is null
)
-- generate a random sequence on remaining cards
, cte_deal as
(
  select CardId, row_number() over(order by CHECKSUM(NewId())) seq
  from AvailableCard ac 
  where not exists (
    select *
    from CurrentHand ch
    where ch.CardId = ac.CardId
    )
)
-- deal the cards
select n.PlayerId, d.CardId
from cte_needed n
inner join cte_deal d on d.seq = n.seq