What is wrong with this sql statement?

Question

I'm expecting this statement to find about 45 Users that do not have this setting in the settings table but it isn't inserting any records. The sql statement is:

INSERT INTO [dbo].Settings SELECT 'ALLOW_WEB_APPROVALS', '1', [Login] FROM Users where [Login] not in (Select UserName from Settings where PropertyField='ALLOW_WEB_APPROVALS')

The username field in settings is the Login field in Users table. What am I doing wrong?

Any help is appreciated.

Thanks

Answer 1

Please post table design as DDL, some sample data as DML statement and the expected result.

Is there may be a UserName NULL? A compare on NULL ends in undefined result and so may don't return any result. Filter NULL out =>

INSERT INTO [dbo].[Settings](PropertyField, ValueField, UserName) 
SELECT 'ALLOW_WEB_APPROVALS', '1', [Login] 
FROM Users 
where [Login] not in (Select UserName 
                      from Settings 
                      where PropertyField='ALLOW_WEB_APPROVALS'
                            and not UserName IS NULL)