• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1686
  • Last Modified:

Error: IDENTITY_INSERT is set to OFF

when i run this insert statement:

INSERT INTO TblStudent
(UsernameID)
SELECT UsernameID AS UsernameID
FROM TblUsername
WHERE (UsernameID = UsernameID)

i get this error: [Microsoft][ODBC SQL server Driver][SQL Server] Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF

I have installed service pack 3.

Do i need to do anything else? Or is there something wrong with my insert statement?
0
Rpatel108
Asked:
Rpatel108
  • 9
  • 7
  • 4
1 Solution
 
Thandava VallepalliCommented:
UsernameID is Identity field... so you can't insert values in this field....  just ignore this field and insert remaining fields...  its gets new value automatically... if you want to insert manually... ( like above )... let me know



itsvtk
0
 
Thandava VallepalliCommented:
OK TRY THIS

=============================

SET IDENTITY_INSERT TblUsername  OFF

INSERT INTO TblStudent
(UsernameID)
SELECT UsernameID AS UsernameID
FROM TblUsername
WHERE (UsernameID = UsernameID)

SET IDENTITY_INSERT TblUsername  ON

==============================


itsvtk



0
 
Rpatel108Author Commented:
what i want to do is insert unique IDs from one table to another table, where one of the column has a value of 1.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Thandava VallepalliCommented:
ok then this works


=================================
SET IDENTITY_INSERT TblUsername  OFF

INSERT INTO TblStudent
(UsernameID)
SELECT UsernameID AS UsernameID
FROM TblUsername
WHERE (UsernameID = UsernameID)

SET IDENTITY_INSERT TblUsername  ON
==================================

itsvtk
0
 
Rpatel108Author Commented:
For some reason it didn't work
0
 
Thandava VallepalliCommented:
Ooops  just reverse...


=================================
SET IDENTITY_INSERT TblUsername  ON

INSERT INTO TblStudent
(UsernameID)
SELECT UsernameID AS UsernameID
FROM TblUsername
WHERE (UsernameID = UsernameID)

SET IDENTITY_INSERT TblUsername  OFF
==================================
0
 
Rpatel108Author Commented:
do i need to change the datatype in the UsernameID column for TblStudent?
0
 
Thandava VallepalliCommented:
For more information about Identity_insert files...


http://www.sqlteam.com/item.asp?ItemID=8003

itsvtk
0
 
HilaireCommented:
Sorry I don't follow what you're trying to achieve
the where clause

WHERE (UsernameID = UsernameID)

is meaningless "as is"
it's the same than

WHERE 1 = 1

ie no condition at all

Is it intended as a reference to another table ?
You 'll need to add aliases or use fully qualified names mytable.mycolumn to be more explicit, and use iether a join or a correlated subquery
0
 
Rpatel108Author Commented:
that sound compliated!

ah basically i want to do this, i have a table for username which has usernames, password, and a column for IsStudent and IsLecturer, and have the values either 1 or 0. 1 indicating that the user is a student or lecturer and 0 indicating they are not.

and then i have another table for student where i want to retrieve all the username IDs and username information, where the IsStudent column has the values 1.

here are the ddls, if it helps:

CREATE TABLE [TblUsername] (
      [UsernameID] [int] IDENTITY (1, 1) NOT NULL ,
      [Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [IsStudent] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [IsLecturer] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Course_ID] [int] NULL ,
      CONSTRAINT [PK_TblUsername2] PRIMARY KEY  CLUSTERED
      (
            [UsernameID]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [TblStudent] (
      [UsernameID] [int] IDENTITY (1, 1) NOT NULL ,
      [FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Course_ID] [int] NULL ,
      [Course_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [IsStudent] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Module_ID] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      CONSTRAINT [PK_TblStudent] PRIMARY KEY  CLUSTERED
      (
            [UsernameID]
      )  ON [PRIMARY] ,
      CONSTRAINT [FK_TblStudent_TblCourse2] FOREIGN KEY
      (
            [Course_ID]
      ) REFERENCES [TblCourse] (
            [Course_ID]
      ) ON UPDATE CASCADE ,
      CONSTRAINT [FK_TblStudent_TblUsername] FOREIGN KEY
      (
            [UsernameID]
      ) REFERENCES [TblUsername] (
            [UsernameID]
      )
) ON [PRIMARY]
GO


0
 
HilaireCommented:
There's a mistake in the design of your TblStudent TABLE

CREATE TABLE [TblStudent] (
     [UsernameID] [int] IDENTITY (1, 1) NOT NULL ,
     [FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
....

should be

CREATE TABLE [TblStudent] (
     [UsernameID] [int] NOT NULL ,
     [FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,



0
 
Thandava VallepalliCommented:
AFTER IMPLEMENTING Hilare  Point...  

here is the query to query the username, pwd.. of students


SELECT T1.[Username], T1.[Password], T2.*
FROM [TblUsername] T1, [TblStudent] T2
WHERE T1.[UsernameID] = T2.[UsernameID] AND [IsStudent] = 1


ITSVTK
0
 
Rpatel108Author Commented:
ah thats why it wasn't working! thank you
0
 
HilaireCommented:
IDENTITY (1, 1) means you want SQL server to autocatically assign a unique sequence number upon record creation

Here you don't want SQL Server to insert an incremental number, since the UsernameID will be used (that's why you have a foreign key) to refer to an existing record in TblUsername.
You'll need to insert the UserNameID s EXPLICITLY (basically waht you were trying to do)
0
 
Rpatel108Author Commented:
Thanks it worked :)
0
 
Thandava VallepalliCommented:
But becarefull while inserting rows in TblStudent and TblLecture tables...   you need to insert same TblUsername.UsernameID in both the tables....

itsvtk
0
 
Rpatel108Author Commented:
Will the TbleStudent automatically populate when a new user has been inserted into tblUsername? Or will i have to use a trigger?
0
 
Thandava VallepalliCommented:
yes you need a trigger here.....  
0
 
Thandava VallepalliCommented:
use below function to insert tblUsername id into student & lecture tables....

SCOPE_IDENTITY( )


itsvtk
0
 
HilaireCommented:
>>Will the TbleStudent automatically populate when a new user has been inserted into tblUsername<<
No

>>Or will i have to use a trigger?<<
A trigger might be an option, another one (better IMHO) would be to use a stored procedure
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 9
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now