Violation Of Primary Key Constraint Query

I was wondering if anyone could help me with my SQL code below. I am trying to insert all of the records from my BC_completions table into my Geodata table. If I exclude the ID col

Solution 1:

This is an issue with your loop and your WHERE constraint for the insert statement. You're selecting all of the records from BC_COMPLETIONS and assigning them to the same PK.

Instead, use the ROW_NUMBER() function to assign your PK which will allow you to do this all at once instead of one record at a time:

DECLARE@PKASINTDECLARE@RECORDCOUNTASINTSET@PK= (SELECT TOP 1 ID FROM PRCORE.DBO.GEODATA ORDERBY ID DESC) +1SET@RECORDCOUNT= (SELECTCOUNT(*) FROM BC_COMPLETIONS)

INSERTINTO PRCORE.DBO.GEODATA (ID,RecordType,ReferenceName,LocationDescription,Description,ORN,StartDate,ChgText)
SELECTROW_NUMBER() OVER(ORDERBY REFVAL) +@PK ,189,REFVAL,ADDRESS,DSCRPN,ORN,RECPTD,AGTNAME
FROM BC_COMPLETIONS B