SQL Server create primary key constraint duplicate key error

I have been experiencing some strange behaviour with one of my SQL commands taken from one of our stored procedures.

This command follows the below order of execution:

1) Drop table

2) Select * into table name from live server

3) Alter table to apply PK – this step fails once out of 4 daily executions

My SQL statement:

 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' 
 [inf].[tblBase_MyTable]') AND type in (N'U'))
 DROP TABLE [inf].[tblBase_MyTable]

 SELECT * INTO [inf].[tblBase_MyTable]
 FROM LiveServer.KMS_ALLOCATION WITH (NOLOCK)

 ALTER TABLE [inf].[tblBase_MyTable] ADD  
 CONSTRAINT [PK_KMS_ALLOCATION] PRIMARY KEY NONCLUSTERED 
 (
   [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = 
 OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

 GRANT SELECT ON [inf].[tblBase_MyTable] TO ourGroup

This is very strange considering the table is dropped, and I thought the indexes / keys would also be dropped. However I get this error at the same time every day. Any advice would be very much appreciated.

Error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘inf.tblBase_MyTable’ and the index name ‘PK_KMS_ALLOCATION’.

Answer

Having reviewed various helpful comments here, I have decided against (for now) implementing SNAPSHOT isolation as this interface does not make use of a proper staging environment.

To move to this would mean either creating a staging area and setting that database to READ COMMITTED SNAPSHOT isolation, and a rebuild of the entire interface.

To that end and on the basis of saving development time, we have opted for ensuring that any ghost reads where dupes could be brought across from the source are handled before applying the PK.

This is by no means an ideal solution in terms of performance on the target server but will provide some headroom for now and certainly remove the previous error.

SQL approach below:

DECLARE @ALLOCTABLE TABLE 
(SEQ INT, ID NVARCHAR(1000), CLASSID NVARCHAR(1000), [VERSION] NVARCHAR(25), [TYPE] 
NVARCHAR(100), VERSIONSEQUENCE NVARCHAR(100), VERSIONSEQUENCE_TO NVARCHAR(100), 
BRANCHID NVARCHAR(100), ISDELETED INT, RESOURCE_CLASS NVARCHAR(25), RESOURCE_ID 
NVARCHAR(100), WARD_ID NVARCHAR(100), ISCOMPLETE INT, TASK_ID NVARCHAR(100));

------- ALLOCATION
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[inf]. 
[tblBase_MyTable]') AND type in (N'U'))
DROP TABLE [inf].[tblBase_MyTable]

SELECT * INTO [inf].[tblBase_MyTable]
FROM LiveServer.KMS_ALLOCATION WITH (NOLOCK)

INSERT INTO @ALLOCTABLE
SELECT *
  FROM
   (SELECT 
   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ISCOMPLETE DESC) SEQ, AL.*
   FROM [inf].[tblBase_MyTable] AL
 )DUPS
 WHERE SEQ >1

 DELETE FROM [inf].[tblBase_MyTable]
 WHERE ID IN (SELECT ID FROM @ALLOCTABLE)
 AND ISCOMPLETE = 0

ALTER TABLE [inf].[tblBase_MyTable] ADD  CONSTRAINT 
[PK_KMS_ALLOCATION] PRIMARY KEY NONCLUSTERED 
  (
   [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GRANT SELECT ON [inf].[tblBase_MyTable] TO OurGroup