Identity Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identity Insert

Author

Topic

pravin14u
Posting Yak Master

246 Posts

Posted - 2007-04-02 : 02:12:30

Hi All,

I tried enabling the IDENTIY_INSERT ON for 2 tabls in a database. It says that I cannot have 2 tables in a database with IDENTITY INSERT ON.

Why is this so?

Thanks,

Prakash.P

Kristen
Test

22859 Posts

Posted - 2007-04-02 : 02:20:04

You can only set IDENTITY_INSERT to ON on a single table at a time. You need to set it to OFF on the first table before you set it to ON on the second.

Kristen

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-04-02 : 03:42:41

Yes that true you can have only one at a time shame really

--------------------------
You only learn by practicing

pravin14u
Posting Yak Master

246 Posts

Posted - 2007-04-02 : 03:50:56

Hi All,

Thanks a lot for your responses.

I am sorry ...I forgot to mention that I am using SQL 2005.

Its hard to believe that even SQL 2005 has this bug??????

Prakash.P

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-02 : 03:57:19

<<
Its hard to believe that even SQL 2005 has this bug??????
>>

How do you say this is bug?

Madhivanan

Failing to plan is Planning to fail

pravin14u
Posting Yak Master

246 Posts

Posted - 2007-04-02 : 04:25:21

Hi Madhi,

Please refer the link below:

http://support.microsoft.com/?kbid=878501

It was a bug with SQL 2000 and I am not sure why SQL 2005 has such a limitation.It would be great if you can tell me the truth behind such a limitation.

Thanks,

Prakash.P

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-02 : 04:29:08

Dear pravin14u,

The link you posted is not about the bug which you are talking about... Please read contents carefully.

And the scenario which you posted is not a bug at all. In fact, BOL has documented this behavior:

quote:
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pravin14u
Posting Yak Master

246 Posts

Posted - 2007-04-02 : 05:17:53

Apologies for the confusion caused.

But I am yet to receive any answer the following

".....if you can tell me the truth behind such a limitation."

Thanks,

Prakash.P

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-02 : 06:31:10

I think you need to contact the vendor behind SQL Server. They are known as Microsoft.
Talk to them and please reply back here what they told you, because I am to numb to bother.

I have learned to live with this "limitation". And I can't say it is bothering me that much.
If I ever need to insert values without existing IDENTITY, I turn it off, one table at a time, just as Kristen suggested.
It makes sense, since you can't insert into two tables at one time.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources