V175 Create DB_customSettingValue recs

[2can2]

Hi, I have the following code to create a record in DB_CustomSettingValues but keep 0 rows effected? Dr#3289 doesn't have a Custom record 'AlarmCode' ? Not sure where I am going wrong as I have used this code before with minor changes. Is it to do with constraints?? Any help appreciated.

Code: Select all

/****************** INSERT CustomSettingValues rec where one doesn't exist for a Specific CustomSetting ******/
DECLARE @SettingName VARCHAR(255)
SET @SettingName = 'AlarmCode'
INSERT INTO DB_CustomSettingValues
SELECT NEWID(), '1AFE3222-E804-47C9-8EA1-39B04283C085', DB_Main.DebtorID, DB_Cust_DH1.AlarmCode,'20 May 2021'
FROM DB_Main Left JOIN
DB_Cust_DH1 ON DB_Main.AccountNo = DB_Cust_DH1.AccountNo
WHERE DB_Main.DebtorID NOT IN (SELECT DB_Main.DebtorID FROM DB_CustomSettingValues WHERE SettingID = (SELECT TOP 1 SettingID FROM DB_CustomSetting WHERE SettingName = @SettingName))
AND DB_Main.TradingStatus = 1
AND DB_Main.AccountNo = '3289'


[perry]

Code: Select all

DECLARE @SettingName VARCHAR(255)
SET @SettingName = 'AlarmCode'
INSERT INTO DB_CustomSettingValues
SELECT NEWID(), '1AFE3222-E804-47C9-8EA1-39B04283C085', DB_Main.DebtorID, DB_Cust_DH1.AlarmCode,'20 May 2021'
FROM            DB_Cust_DH1 INNER JOIN
                         dbo.DB_Main ON DB_Cust_DH1.AccountNo = dbo.DB_Main.AccountNo LEFT OUTER JOIN
                         dbo.DB_CustomSetting INNER JOIN
                         dbo.DB_CustomSettingValues ON dbo.DB_CustomSetting.SettingID = dbo.DB_CustomSettingValues.SettingID AND dbo.DB_CustomSetting.SettingName = @SettingName ON
                         dbo.DB_Main.DebtorID = dbo.DB_CustomSettingValues.DebtorID
WHERE        (dbo.DB_CustomSetting.SettingID IS NULL) AND (dbo.DB_Main.TradingStatus = 1)

[2can2]

Thanks Perry. Much appreciated!