Why can't I insert into this table?

Question

Really, I'm not sure what to ask.

I'm inserting into a table that has a UNIQUEIDENTIFIER for a primary key. IDENTITY_INSERT is off, and so I thought that I would be able to just insert without including a value for the GUID.

But when I run this script:

DECLARE @temp TABLE (id UNIQUEIDENTIFIER)

INSERT INTO sBound( --line 4
        cPolNum, cName1, dBound, dExpire, ... ~ 40 other columns
        nQuoteNum
        )
OUTPUT INSERTED.iRowID INTO @temp (id) 
(SELECT TOP 1
        cPolNum, cName1, dBound, dExpire, ... ~ 40 other columns
        3680189 
    FROM
        sBound 
    WHERE
        iRowID = '14863E9C-6AE7-4B7A-916D-038ECBFA4668');

DECLARE @newID UNIQUEIDENTIFIER
SELECT @newID = id
FROM @temp;

UPDATE sQuote --line 21
    SET 
        iRowID = @newID
    WHERE
        iRowID = 'AA030454-BFC9-E411-B421-0015176857B6'

I'm getting this error:

Msg 544, Level 16, State 1, Line 4
Cannot insert explicit value for identity column in table 'sBound' when IDENTITY_INSERT is set to OFF.
Msg 515, Level 16, State 2, Line 21
Cannot insert the value NULL into column 'iRowID', table 'NREngine.dbo.sQuote'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Why is it telling me that I can't insert a value for the identity column? I didn't provide a value for the identity column. What should I be looking for?

How do I insert into this if I can't explicitly provide a value, and can't not provide a value?

Perhaps I'm misunderstanding, but if you turn off identity insert, you need to provide an identity in your query manually. — crthompson, Mar 18 '15 at 18:28
The reason that the second error is happening is because the output in line 9 is null (nothing was inserted). The identity column in `sBound` is `iRowID` for which no value was provided. — Joseph Nields, Mar 18 '15 at 18:30
An identity column cannot be a GUID. I think you're mistaken on which column in the table is the identity column. — Joe Stefanelli, Mar 18 '15 at 18:31
http://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity — nobody, Mar 18 '15 at 18:32
@JoeStefanelli untrue. I'm looking at it right now... `iRowID (PK, uniqueidentifier)`. It has a key to the left of it.... — Joseph Nields, Mar 18 '15 at 18:32
@JosephNields: That just means it's the PRIMARY KEY. That DOES NOT mean it is an IDENTITY COLUMN. Run this: `SP_HELP 'sBound'`. That should help you identify the identity column. — Joe Stefanelli, Mar 18 '15 at 18:33
As a side note, if you are using a GUID as a primary key and it is also your primary index, you will create a random sort on your table. Depending on the type of queries you are running, it could significantly affect performance. — crthompson, Mar 18 '15 at 18:38
can you make that an answer @JoeStefanelli? That's where I was off. It solved my problem. — Joseph Nields, Mar 18 '15 at 18:38

score 2 · Accepted Answer · answered Mar 18 '15 at 18:39

2

An identity column cannot be a GUID.

Run this to help you find which column is the identity in the table:

SP_HELP 'sBound'

answered Mar 18 '15 at 18:39

Joe Stefanelli

132,803
19
237
235

Why can't I insert into this table?

1 Answers1