identity columns 1

[CRuser89]

Hello,

I have the following sql script:

insert into table1 values ('1', 'testdata', '999')

insert into table1 values ('2', 'testdata2', '998')

I get the error below because the first column is an identity column. I know that I can leave the first column blank and it will insert it but is there any way for me to force it to take a number?

An explicit value for the identity column in table 'table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.


Thanks,
Tracy

 

[jbenson001]

Just what the error says, you have to set Identity_insert ON.

 

[CRuser89]

Thank you for the quick reply jbenson001.

I added it to my sql script as follows

SET IDENTITY_INSERT tblx_Division ON
insert into table1 values ('1', 'testdata', '999')
GO
SET IDENTITY_INSERT tblx_Division OFF


and I still get the same error message. Am I using it incorrectly? Sorry to be such a pain, i'm very new to sql server.

Thanks,
Tracy

 

[jbenson001]

Try using the column names in the Insert

SET IDENTITY_INSERT tblx_Division ON
insert into table1(col1, col2, col3)
values GO
SET IDENTITY_INSERT tblx_Division OFF('1', 'testdata', '999')

 

[CRuser89]

Thanks so much Jbenson

 

[jbenson001]

Did that work for you? It was the only thing I could see wrong with what you wree doing...

Jim

 

[CRuser89]

Hi Jbenson,

Yes, that did the trick. Thanks again for your help.

Tracy

 

[jbenson001]

ok great.. glad to help.

Jim