TURNING OFF IDENTITY

if i want to be able to manually insert the value of a primary key do i want IDENTITY_INSERT to be on or off?

scm0smlAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you have to first turn off the IDENTITY_INSERT on the other table, and for the future, always turn off the identity_insert once you did complete the insert
SET IDENTITY_INSERT TEAMS OFF
SET IDENTITY_INSERT CLUBTEAMS ON
INSERT INTO CLUBTEAMS(PK_CLUBTEAMID, TEAMDESCRIPTION) VALUES (1, '1st Team')  
SET IDENTITY_INSERT CLUBTEAMS OFF

Open in new window

0
 
scm0smlAuthor Commented:
if i try and do the following:
INSERT INTO CLUBTEAMS(PK_CLUBTEAMID, TEAMDESCRIPTION) VALUES (1, '1st Team')

i get:
Cannot insert explicit value for identity column in table 'CLUBTEAMS' when IDENTITY_INSERT is set to OFF.

so if i do:
SET IDENTITY_INSERT CLUBTEAMS on

i get:
IDENTITY_INSERT is already ON for table 'TaffsWellAFC.dbo.TEAMS'. Cannot perform SET operation for table 'dbo.CLUBTEAMS'.

whats going on?
0
 
scm0smlAuthor Commented:
there is something very strange going on here.

look at the table names....why are they changing. i do have a table called teams but i am definately running the script that i have included in this post.
0
 
erikTsomikSystem Architect, CF programmer Commented:
You better keep the identity off because of all the integrities
0
 
scm0smlAuthor Commented:
i only want to do it temp so that i can import a load of data from another db and need to keep all my f'keys etc. then it will be going back on.

i only have sql server 2005 express so have to do the import like this
0
All Courses

From novice to tech pro — start learning today.