CODEWITHSUNDEEP
sqlinsertexists
user1673665
user1673665

Reputation: 522

INSERT INTO Statement only for those who don't have already an entry

I have 2 tables: cSc_UserClassSettings and cSc_User

I want to INSERT all User in cSc_UserClassSettings from cSc_User using this INSERT Statement:

INSERT INTO [cSc_UserClassSettings]
       ([RSRC]
       ,[Deleted]
       ,[DateNew]
       ,[DateChanged]
       ,[UserNew]
       ,[UserChanged]
       ,[camosGUID]
       ,[UserGUID]
       ,[ClassName]
       ,[WriteByOpen]
       ,[DefaultReadAccess]
       ,[DefaultWriteAccess]
       ,[Summary])
SELECT
        0
       ,0
       ,getdate()
       ,NULL
       ,camosGUID
       ,NULL
       ,NEWID()
       ,camosGUID
       ,'cQ_RootOffer_C'
       ,0
       ,0
       ,0
       ,0
FROM [cSc_User]

But some users already have an entry in cSc_UserClassSettings and for those it should not make an entry.

If it is neccessary cSc_UserClassSettings has a Foreign Key on cSc_User with cSC_UserClassSettings.UserGUID = cSC_User.camosGUID

I've tried this:

INSERT INTO [cSc_UserClassSettings]
       ([RSRC]
       ,[Deleted]
       ,[DateNew]
       ,[DateChanged]
       ,[UserNew]
       ,[UserChanged]
       ,[camosGUID]
       ,[UserGUID]
       ,[ClassName]
       ,[WriteByOpen]
       ,[DefaultReadAccess]
       ,[DefaultWriteAccess]
       ,[Summary])
SELECT
        0
       ,0
       ,getdate()
       ,NULL
       ,camosGUID
       ,NULL
       ,NEWID()
       ,camosGUID
       ,'cQ_RootOffer_C'
       ,0
       ,0
       ,0
       ,0
FROM [cSc_User]
WHERE NOT EXISTS(
select *
from cSc_UserClassSettings
where classname = 'cQ_RootOffer_C' and deleted = 0
)

But it returns 0 values. Any ideas how I could solve that?

Thanks in advance.

Upvotes: 0

Views: 33

Answers (1)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8507

If you want to insert record in cSc_UserClassSettings from cSC_User table but those are not in already in cSc_UserClassSettings then you can try this Select Query

SELECT
        0
       ,0
       ,getdate()
       ,NULL
       ,camosGUID
       ,NULL
       ,NEWID()
       ,camosGUID
       ,'cQ_RootOffer_C'
       ,0
       ,0
       ,0
       ,0
FROM [cSc_User] 
WHERE cSC_User.camosGUID NOT IN (
                      Select UserGUID
                      From cSc_UserClassSettings
                      )

Upvotes: 1

Related Questions