I'm trying to insert into table with the not in condition
but doesn't seem to work
Here is the data
VCENTER ALERT
A-PROD Alert
A-PROD Warning
A-UAT NULL
B-UAT NULL
C-PROD Warning
C-UAT NULL
D-PROD Alert
E-PROD NULL
Here is my sql
DECLARE @AMRSVCentersIDX TABLE
(
[VCENTER] nvarchar(200),
[ALERT] varchar(20),
[ID] int
)
INSERT INTO @AMRSVCentersIDX
SELECT DISTINCT tgh.[ENVIRONMENT], RIGHT(tha.AlertType, CHARINDEX(' ', REVERSE(tha.AlertType)) - 1) as [ALERT],
ROW_NUMBER() OVER (Order by tgh.[ENVIRONMENT]) AS [ID]
FROM [Reporting].[dbo].[tblGEN2_HOSTS] tgh WITH (NOLOCK)
LEFT JOIN (SELECT DISTINCT [DataCenter], [AlertType] FROM [Reporting].[dbo].[tblHostAlerts] WITH (NOLOCK) WHERE [Duration] >= 10) tha ON tgh.[ENVIRONMENT] = tha.[DataCenter]
WHERE tgh.[ENVIRONMENT] NOT LIKE '%DEV%' AND tgh.[REGION] = 'AMRS'
AND tgh.[ENVIRONMENT] NOT IN (SELECT '''' + [VCENTER] + '''' as [ENVIRONMENT] FROM @AMRSVCentersIDX)
What I expect to be inserted into the temp table is
VCENTER ALERT ID
A-PROD Alert 1
A-UAT NULL 2
B-UAT NULL 3
C-PROD Warning 4
C-UAT NULL 5
D-PROD Alert 6
E-PROD NULL 7
Any help would be most appreciated
Regards,
M