Cannot insert row into table

Hello

Although I am able to delete a row from CatA table, I am unable to insert a row into the same table.

The rows are from 1 to 241, I accidentally deleted row 41, together with row 42 which I wanted to delete. But I can't reinsert row 41.

I see this message:

INSERT INTO [dbo].[CatA]([CatA_ID], [LName], [Auto], [Advertising], [Antiques], [Art], [Astrology])
VALUES(41, N'JSmith', '(null)', '(null)', '(null)', '(null)', '(null)')

Error: Query(1/2) Cannot insert explicit value for identity column in table 'CatA' when IDENTITY_INSERT is set to OFF.

Below are the settings for this table, I have tried to change the settings but I am prevented.

CatA_ID = Primary Key

Parameter and Value:

Identity = yes

Identity Seed = 1

Identity Increment = 1

IsRowGuidCol = No

What do I need to do, how do I reinsert the row 41 or insert a new row in the future?

Please advise.

Regards

Lynn

with an auto-incrementing field, you never use that field in your insert statement - it, by it's name, auto-increments whenever a row is added to the database table. You can't insert to a specific row, using the auto-increment table.

If you deleted item 41 and item 42 - the next item inserted will be item 43. You really don't need to worry that the auto-incrementing fields are not fully sequential - if they are missing some numbers, it doesn't matter at all.

remember, when inserting into the table, do not use the identity/auto-incrementing field in your insert statement.

|||

Hello augustwind

Thanks for the reply. When I was creating the tables, I just ticked the boxes. Is there a way I can undo this?

I don't mind if the numbers are not sequential, but I just can't insert a row.

If I try to insert using the next number in sequence or number 42 I get the error mentioned in my first post.

My database is SQL 2000 and I am editing with Aqua Data Studio.

I have the option to insert data directly into the table, but when I tried without inserting a number I saw this error.

INSERT INTO [dbo].[CatA]()
VALUES()

Error: Query(1/1) Line 2: Incorrect syntax near ')'.

How do I insert a row? If I need to use syntax, what is the correct syntax required?

Or how do I remove the auto insert in my table?

Thanks.

Regards

Lynn

|||

Hi

If you accidentally delete a record with an identity column you could follow those to restore it:

Set the Identity Specification(is Identity) of the identity column toNo -->insert the record you deleted -->Set the Identity Specification(is Identity) of the identity column toyes

Hope this helps.

|||

Hello

Thanks for your reply and help.

I now know that I am unable to insert data directly into the row.

Below is the syntex which I have found to work and which might assist other sql beginners.

SET IDENTITY_INSERT CatA OFF

INSERT INTO CatA ( [LName])
VALUES( N'SJones')

GO

Thanks

Lynn

|||Hi Lynn, the error message you mentioned in the first post should give you some prompt, you can temporarily turn onIDENTITY_INSERT when you want to insert a row with specifying identity number:

SET IDENTITY_INSERT?CatA ON

INSERT INTO [dbo].[CatA]([CatA_ID], [LName], [Auto], [Advertising], [Antiques], [Art], [Astrology])
VALUES(41, N'JSmith', '(null)', '(null)', '(null)', '(null)', '(null)')

SET IDENTITY_INSERT?CatA OFF