We help IT Professionals succeed at work.

Set Identity_insert on does not work

I have the following statements in the Query analyser (SQL server 2000).... can not be simpler

SET IDENTITY_insert attendancetype on
insert into attendancetype select * from members..attendancetype
SET IDENTITY_insert attendancetype off

and I am getting a failure message
An explicit value for the identity column in table 'attendancetype' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I tried every thing including rebooting my computer.... no change... What is wrong in thse statements, please help. There is no other table in the session that has IDENTITY_INSERT On in the session, I restarted new session of query analyser, but no use. Is there a way to know what can be the reason.
Thanks In advance

Watch Question

Database Consultant
Top Expert 2009
You need to explicitly mention the columns

insert into attendancetype(Colmn1, col2, col3 )   select col1,col2,col3  from members..attendancetype


Thanks aneesshattingal

Explore More ContentExplore courses, solutions, and other research materials related to this topic.