IDENTITY_INSERT issue

Last Post 09 Nov 2011 02:09 AM by cora. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
kjmcsd
New Member
New Member

--
15 Jun 2011 12:12 PM
I am an instructor at a university. I am in a classroom with 10 students using SS 2008 Express. Only one of my students is having an issue when doing inserts into a table. He gets an error say IDENTITY_INSERT needs to be turned ON. So I do a SET IDENTITY_INSERT ON. Later if he does an INSERT into another table he has to turn IDENTITY_INSERT OFF for the previous table and turn it ON for the table he now wants to INSERT into.

Why does he have to do this? Is there a setting I can just turn off to make it so he does not have to SET IDENTITY_INSERT each time?
rm
New Member
New Member

--
15 Jun 2011 12:36 PM
Sql will generate value for identity column based on seed and step settings. If you want to put in your own value, then have to set identity insert on.
russellb
New Member
New Member

--
16 Jun 2011 05:49 AM
If it's only happening for one student of your 10, then either, he has identity columns in hs tables, and the other students don't, OR he is specifying the identity value and the others aren't. As rm said, SQL Server will generate this value for you. If you're going to specify, then you must set identity_insert on. To have SQL generate it, simply omit it from the list.

Example:

Create Table t1 (a int identity(1, 1), b int, c int);
GO
INSERT t1 (b, c)
VALUES (1, 2);
GO
cora
New Member
New Member

--
09 Nov 2011 02:09 AM
In order to complete insertion of the tables in the database of SQL first of all you require to make a database. In case you have made database then you need to type 
insert into table tablename select * from tablename;
This process will help you to show all the information present in the table.
In case problem is seen then it is possible the database is damaged. You should try using stellar sql recovery application. It is helpful. 


Acceptable Use Policy
---