remove identity property

Question

remove identity property

elham_azizi_62

SSCertifiable

Points: 5095
More actions
August 28, 2012 at 11:36 pm

#263360

hello all.
I want to insert into identity column and i want to first remove identity property and insert value and then enable identity property.I use this script:SET IDENTITY_INSERT IdentityTable ON
INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')
SET IDENTITY_INSERT IdentityTable OFF but get error :Cannot insert explicit value for identity column . ho w do i do for this goal?please guide me.thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 1

Permissions issue? IDENTITY_INSERT requires that you are the owner of the table or are a member of db_owner or db_ddladmin on the database.

Have you correctly qualified the table name with the schema?

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 2

I login with sa.is that enough?how to member of db_owner or db_owner?

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 3

Permissions are not the issue then if you're a member of sysadmins.

I'm not sure what else can be the issue; its a fairly straightforward process usually. When you run SET IDENTITY_INSERT IdentityTable ON; do you get any error message?

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 4

sa is db_owner and when I select dbo.ddladmin for my database ,get error:can not use the special principal dbo.

how do i do?

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 5

my error just is:

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

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 6

As I said in my last post, if you're running the code as a member of sysadmins then permissions are not a problem and you don't need to do anything.

Are you getting any error message when you execute SET IDENTITY_INSERT IdentityTable ON; ?

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 7

my error :

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

and I have only this error

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 8

Ok, so I do this...

SET IDENTITY_INSERT dbo.TestTable ON

and get this...

[font="Courier New"]Command(s) completed successfully.[/font]

What message do you get?

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 9

yes get command complete succesfully.

but when insert into identity table get error:

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

In the batch where you do the insert you need to set identity insert on (as Clare explained) and you need to list the columns that the insert affects. Both are well explained in Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 11

Keep in mind that just because a column has the Identity property that's no guarantee that values in the column will be unique or consecutive. Usually (but not always) Identity cols are set to have unique values so in that case the source values will also have to be unique or you'll get an error even with IDENTITY_INSERT on.

SET IDENTITY_INSERT dest_table ON

INSERT INTO dest_table (IdentCol, Col2, Col3)

SELECT

X AS IdentCol

,'Y' AS Col2

,'Z' AS Col3

FROM source_table

SET IDENTITY_INSERT dest_table OFF

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 12

elham_azizi_62 (8/29/2012)
yes get command complete succesfully.
but when insert into identity table get error:
An explicit value for the identity column in table 'WOS_WorkOrderType' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So, I notice that in your last post you received the above error. However in your initial post you said the error was:

elham_azizi_62 (8/29/2012)
Cannot insert explicit value for identity column

As others have said, you need to be sure you are using the commands correctly. You must specify a column list in your INSERT statement. That is the issue with the second error message you posted, which differs from your original problem, which suggests that you had not set IDENTITY_INSERT to ON for the table you were attempting to insert to.