Prevent duplicate rows being inserted

Question

I'm trying to use an SQL insert statement to migrate rows from a table in one database to a table in a different database. The statement works until I add a unique index on the destination table and at that point I'm struggling to get the insert statement to be able to exclude the duplicates. Here's what I though should work:

INSERT INTO [MyDB].[dbo].[HPB] (
    [HPID],
    [BusinessID]
)
SELECT 
    PersonId = (SELECT ID FROM [MyDB].[dbo].[HP] WHERE PersonID = lPersonId),
    lBusinessId
FROM [MyOriginalDB].[dbo].[tblEmployment]
WHERE
    lPersonId in (SELECT PersonID FROM [MyDB].[dbo].[HP])
AND
    lBusinessId in (SELECT ID FROM [MyDB].[dbo].[Business])
AND 
   NOT EXISTS (SELECT * FROM [MyDB].[dbo].[HPB] WHERE 
     [HPID] = (SELECT ID FROM [MyDB].[dbo].[HP] WHERE PersonID = lPersonId) 
     AND [BusinessID] = lBusinessId)

The schema for the HPB table is:

CREATE TABLE [dbo].[HPB](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [HPID] [int] NOT NULL,
   [BusinessID] [int] NOT NULL,
   CONSTRAINT [PK_HealthProfessionalBusiness] PRIMARY KEY CLUSTERED)

The unique index is on the [MyDB].[dbo].[HPB] table for columns (HPID, BusinessID)

When I run the insert I get an error about duplicate row inserts and I can't work out why the SQL below doesn't exclude the duplicates.

 NOT EXISTS (SELECT * FROM [MyDB].[dbo].[HPB] WHERE 
     [HPID] = (SELECT ID FROM [MyDB].[dbo].[HP] WHERE PersonID = lPersonId) 
     AND [BusinessID] = lBusinessId)

Answer 1

Use:

INSERT INTO [MyDB].[dbo].[HPB] 
  ([HPID], [BusinessID])
SELECT DISTINCT
       h.id,
       e.lbusinessid
  FROM [MyOriginalDB].[dbo].[tblEmployment] e
  JOIN [MyDB].[dbo].[HP] h ON h.personid = e.lpersonid
 WHERE e.lbusinessid in (SELECT ID FROM [MyDB].[dbo].[Business])
   AND NOT EXISTS (SELECT NULL 
                     FROM [MyDB].[dbo].[HPB] hb
                    WHERE hb.businessid = e.lbusinessid
                      AND hb.hpid = h.id)

Answer 2

Insert MyDB.dbo.HPB( HPID, BusinessID )  
Select HP.ID, E.IBusinessID  
From [MyOriginalDB].[dbo].[tblEmployment] As E
    Join [MyDB].[dbo].[HP] As HP
        On HP.PersonId = E.IPersonID
    Join [MyDB].[dbo].[Business] As B
        On B.ID = E.IBusinessID
    Left Join [MyDB].[dbo].[HPB] As HPB
        On HPB.BusinessID = E.IBusinessID
            And HPB.PersonID = E.IPersonId
Where HPB.ID Is Null
Group By HP.ID, E.IBusinessID