Insert statement fails with autoincrementing key 1

[ccampbell]

HELP!!! I have an insert statement that is failing. Here is the criterion.

INSERT INTO TEMP1 (name, address, phone)
SELECT DISTINCT name, address, phone FROM XML_TEMP1

Right now, table TEMP1 only has 4 columns
sequence_number (autoincrementing key)
name
address
phone

XML_TEMP1 has 3 keys
name
address
phone

The error that I get is
Server: Msg 515, Level 16, State 2, Line 6
Cannot insert the value NULL into column 'SEQUENCE_NUMBER', table 'Testing.dbo.TEMP1; column does not allow nulls. INSERT fails.
The statement has been terminated.

Any ideas?

 

[SQLSister]

Frankly that sounds to me like your field is either not set up as an identity or you have the setidenty_insert property for the table to ON

 

[ccampbell]

How would I check to see if it is an identity issue? I am told by the database designer that the field (sequence_number) should be autoincrementing.

 

[SQLSister]

If you go into the desing mode for the table in Enterprise Manager look at the field and at the bottum of the window will be properties for that field. One of them is Identity, it should say yes. Or if you want to look at the script for the table you should see the Identity keyword next to the field name after the definition as Integer or some other numeric type.

AS you can see from teh following script it it iseasy to see what the identity field is for this table:
CREATE TABLE [dbo].[CityLocation] (
[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Latitude] [decimal](10, 3) NULL ,
[Longitude] [decimal](10, 3) NULL ,
[CityLatLonID] [int] IDENTITY (1, 1) NOT NULL ,
[InfoType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

 

[ccampbell]

Okay, I have checked that and it was set to NO. Now that brings me to another question... what does the identity field do and why is it needed? THe writer of this database assures me that the field that has an identity set to NO is an autoincrementing field so where does the identity come into play, is that what determines autoincrementation? Thanks for your help and suggestions. I really appreaciate the advice.

 

[jackfl]

The way you set up an auto-number field in SQL is with Identity = Yes. If Identity is set to No and Allow Nulls is not checked then you will get an error when you don't explicitely INSERT this field as well.

 

[ccampbell]

Thanks, that is what I needed to know. I appreciate all of the help and advice.