Auto-Incrementing field in SQL Server?

[blakemckenna]

I am trying to manually add a row of data to a table within SQL Server 2005. The first column, called "MyData_Notes_ID" has the "Is Identity" set to "Yes" (it's also the Primary Key for the table), therefore, I don't include that column in my "INSERT" statement. However, when I execute the statement, it tells me that the "MyData_Notes_ID" field can't be null. What am I doing wrong?

Thanks,

[GaryMazzone]

What is the Insert statement?

[blakemckenna]

Here ya go Gary,

Use OA_Runtime

INSERT INTO MyData_Notes
	(Category,
	 Title,
	 Body,
	 NTLogin,
	 isCurrentVersion,
	 isDeleted,
	 DateCreated,
	 DateDeleted)
  VALUES
	('Address Info',
	 'Wrong Address',
	 'This is test record #2',
	 'Z126409',
	 0,
	 0,
	 GetDate(),
	 GetDate())

[GaryMazzone]

That looks correct. Are you sure that the MyData_Notes_ID is set to IDENTITY?

[blakemckenna]

Gary,

I think I found the problem. You can't have more than 1 identity column per table can you? If so, that was my problem...if not, then I don't know! LOL

[jmcilhinney]

Gary,

I think I found the problem. You can't have more than 1 identity column per table can you? If so, that was my problem...if not, then I don't know! LOL

That would indeed be the problem. It doesn't make sense to have more than one identity per table. The point of the identity is to uniquely identify each record. Two identities can't do that any better than one, so there's no reason to have two.