0

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 column on the insert, I get a 'Msg 515... Cannot insert the value NULL into column 'ID'. But if I include it as below I get a 'Msg ...Violation of PRIMARY KEY constraint'.

What makes it more confusing is that if I manually typed in the value I get from @PK the database accepts it, so any help here would be great.

Thanks

DECLARE @PK AS INT
DECLARE @COUNT AS INT
DECLARE @RECORDCOUNT AS INT

SET @PK = 
(SELECT TOP 1 ID FROM PRCORE.DBO.GEODATA
ORDER BY ID DESC)


SET @RECORDCOUNT =
(SELECT  COUNT(*) FROM BC_COMPLETIONS)


SET @COUNT = 0

WHILE @COUNT < @RECORDCOUNT  
BEGIN
SET @PK = @PK+1
SET @COUNT = @COUNT+1

INSERT INTO PRCORE.DBO.GEODATA     
(ID,RecordType,ReferenceName,LocationDescription,Description,ORN,StartDate,ChgText)
SELECT @PK,189,REFVAL,ADDRESS,DSCRPN,ORN,RECPTD,AGTNAME
FROM BC_COMPLETIONS B

where @PK not in (select ID from prcore.dbo.geodata)

END
SteveC81
  • 35
  • 1
  • 8
  • 1
    Why not just use an auto-incrementing identity column so you don't have to do any of this? – Michael McGriff Sep 17 '14 at 13:27
  • Hi Michael, because I don't know how to and I never created the Geodata table so would not know how to check to see if this is the case for this ID column already. – SteveC81 Sep 17 '14 at 13:31
  • If you're using SSMS right click on the table, select Script Table as -> CREATE TO -> New Query Editor Window. Copy and paste the results of that above. It will show you everything about the table, including if this is how it was created. – Michael McGriff Sep 17 '14 at 13:33
  • Thanks Michael - I also discovered it by looking at the column properties of the ID column, Identity Specification is set to No. So my understanding is that this isn't an auto-incrementing column. – SteveC81 Sep 17 '14 at 13:39

1 Answers1

3

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 @PK AS INT
DECLARE @RECORDCOUNT AS INT

SET @PK = (SELECT TOP 1 ID FROM PRCORE.DBO.GEODATA ORDER BY ID DESC) + 1

SET @RECORDCOUNT = (SELECT  COUNT(*) FROM BC_COMPLETIONS)

INSERT INTO PRCORE.DBO.GEODATA (ID,RecordType,ReferenceName,LocationDescription,Description,ORN,StartDate,ChgText)
SELECT ROW_NUMBER() OVER(ORDER BY REFVAL) + @PK ,189,REFVAL,ADDRESS,DSCRPN,ORN,RECPTD,AGTNAME
FROM BC_COMPLETIONS B
Nick H.
  • 1,616
  • 14
  • 20
  • 1
    Hi Nick - Thanks for this, this worked perfectly! It also helps to know how I can find out the row number as I wasn't sure of this either!! – SteveC81 Sep 17 '14 at 13:42