My insert statement is not inserting any rows-CodePudding

I have two tables that look like this:

I can see that I have 90 games that do not have a corresponding id in my gamePlayers table with this query:

SELECT * 
FROM dbo.game
WHERE id NOT IN (SELECT gameId FROM dbo.gamePlayers)

So I want to create entries for the missing games and add the value '1' for the playerType, and the id of the game for gameId.

So I tried this:

INSERT INTO dbo.gamePlayers
    SELECT 1, p.id 
    FROM dbo.game p
    WHERE p.id NOT IN (SELECT gameId FROM dbo.gamePlayers)

But it doesn't insert anything at all.

Shouldn't it insert 90 rows?

Thanks!

CodePudding user response：

Does the following slight refactor work for you?

insert into dbo.gamePlayers(playerType, gameId)
select 1, p.id 
from dbo.game p
where not exists (select * from gamePlayers gp where gp.gameId=p.Id)