IDENTITY - cannot turn it off

Question

IDENTITY - cannot turn it off

JMI

SSChampion

Points: 11831
More actions
June 2, 2004 at 10:08 am

#108448

I have
CREATE TABLE [dbo].[File] (
[FileID] [int] IDENTITY (1, 1) NOT NULL ,
....,
....
I need to insert 20 records
from 'restore_file' table
Of course it'll give me an error
so I try to remove IDENTITY option
in EM in table Design.
But it hangs and and freezes EM.
I have to close MMC.
[File] table has 44000 records.
FIELD 'FileBinary' stores Binary data.
I was wondering if there is a
a short command to DISABLE IDENTITY?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

Yes, but you'll have to issue it in Query Analyzer not Enterprise Manager. Make certain that any insert queries running while you have it turned off are inserting an identity or it will fail. This is a connection level setting, so it only affects the queries you are running as part of that connection. In other words, identity insert will still be off for other users.

SET IDENTITY_INSERT ON

Insert Query .....

SET IDENTITY_INSERT OFF

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

ashcroal Old Hand Points: 329 More actions · Answer 2

Rawhides answer is almost exact, but you need to specify the table name as well

set identity_insert dbo.File on

'now insert your rows

set identity_insert dbo.File off

should allow you to do it.

Cheers

Al

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

How dare you correct me!!!

Just kidding. Thanks, I did leave a very important part out.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

ashcroal Old Hand Points: 329 More actions · Answer 4

ashcroal

Old Hand

Points: 329

June 2, 2004 at 8:35 pm

#508756

I didn't correct, I just added to it

JMI SSChampion Points: 11831 More actions · Answer 5

Thank you all guys for

your response.

Just to be completely clear about

the command...

So if I 'm connected in Query Analyzer

as "user51" and turn IDENTITY INSERT

ON and then I open another

QA window and run INSERT

am I still affected by command from

the first window?

In other words is connections based

or user based?

buddy__a Hall of Fame Points: 3222 More actions · Answer 6

It must be done in the same QA window in which you executed the SET IDENTITY_INSERT statement.