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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.