How to check for clustered unique key while inserting in SQL table

I am trying to insert rows from another database table to new database table getting the below error if there is no where condition in the query.

Violation of UNIQUE KEY constraint ‘NK_LkupxViolations’. Cannot insert duplicate key in object ‘dbo.LkupxViolation’. The duplicate key value is (00000000-0000-0000-0000-000000000000, (Not Specified)).

Then I wrote the below query adding where conditions it worked but it didn’t insert the expected no. of rows.

IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'LkupxViolation')
BEGIN
   INSERT INTO dbo.[LkupxViolation] SELECT * FROM  [DMO_DB].[dbo].[LkupxViolation] where CGRootId not in (select CGRootId from dbo.[LkupxViolation])
   and Name not in (select name from dbo.[LkupxViolation])
END
ELSE 
PRINT 'LkupxViolation table does not exist'

The unique key in the table is created as:

CONSTRAINT [NK_LkupxViolations] UNIQUE CLUSTERED
(
    [CGRootId] ASC,
    [Name] ASC
)

Answer

Try using NOT EXISTS:

INSERT INTO dbo.[LkupxViolation]
    SELECT *
    FROM  [DMO_DB].[dbo].[LkupxViolation] remove_l
    WHERE NOT EXISTS (SELECT 1
                      FROM dbo.[LkupxViolation] local_l
                      WHERE local_l.Name = remote_l.Name AND
                            local_l.CGRootId = remote_l.CGRootId
                     );

This checks for both values in the same row. In addition, NOT IN is not NULL-safe. If any values generated by the subquery are NULL then all rows are filtered out.