Error number 8101 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error number 8101

Author

Topic

rohan.rishi.2007
Starting Member

8 Posts

Posted - 2010-09-30 : 07:41:30

Hello 2 All

Insert into xxxStud1 Select * from Stud0809 Where Classno=10 and Sessionno =2

i m trying 2 execute this query ...in Sql Server but it gives me error that

Msg 8101, Level 16, State 1, Line 2

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

Please help me ...with this.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-30 : 07:58:10

You have an identity column (auto generated value column) for which you can not give the value explicitly.

instead you try

insert into xxxstud1 ( <column list except identity column> )
SELECT <Respective column list> FROM stud0809 WHERE Classno=10 and Sessionno =2

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 11:59:54

and if you want to insert an explicit value to identity column make sure you set IDENTITY_INSERT to ON on that table before that.

ie.


SET IDENTITY_INSERT xxxStud1 ON

Insert into xxxStud1 Select * from Stud0809 Where Classno=10 and Sessionno =2 

SET IDENTITY_INSERT xxxStud1 OFF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources