CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Joseph Nields
Joseph Nields

Reputation: 5661

Why can't I insert into this table?

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?

Upvotes: 0

Views: 522

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

An identity column cannot be a GUID.

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

SP_HELP 'sBound'

Upvotes: 2

Related Questions