Get value of 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Get value of IDENTITY_INSERT

Author

Topic

crö
Starting Member

6 Posts

Posted - 2009-12-10 : 06:16:11

Hi

I'm not able to find out, how to check the actual value of the table-option IDENTITY_INSERT. I can set it by SET IDENTITY_INSERT [table_name] ON/OFF – but how can I get the value back??

Thank you for a hint.
crö

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-10 : 06:19:08

Hi

You mean this...

IDENT_SEED
Returns the original seed value

-------------------------
R...

crö
Starting Member

6 Posts

Posted - 2009-12-10 : 07:09:01

Hey rajdaksha, thanks for the reply.

I didn't mean that. IDENT_SEED gives back the starting value of an identity column.

IDENTITY_INSERTED on the other hand is an option that determines whether a particular value can be inserted into an identity column or not.

My question now is how to get the actual value of this option...

crö

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-10 : 07:17:36

Do you mean the row you have just inserted? If so then SCOPE_IDENTITY() will return it.

All that IDENTITY_INSERT does is tell SQL whether you want to explicitly insert a certain value into the identity field of a table, so if you already had a table:


create table test (a int identity(1,1) not null, b varchar(20))
insert into test
select 'this will be 1'
union select 'this will be 2'
...

If you then wanted to insert a value explicity to the table, you would do:


set identity_insert test on
insert into test (a,b)
select 4,'this will be 4'
set identity_insert test off

If you wanted the next identity to be returned so that you know what it is:


declare @i int
insert into test
select 'whats this value?'

set @i = SCOPE_IDENTITY()
select @i, 'this is the value just inserted'

crö
Starting Member

6 Posts

Posted - 2009-12-10 : 07:59:23

Unfortunately not...

I try to explain it refering your example:


...

set identity_insert test on
insert into test (a,b)

At this point of the script, the value of the option IDENTITY_INSERT is ON.
=> How can I get that via script??


select 4,'this will be 4'
set identity_insert test off

And at this point of the script, the value of the option IDENTITY_INSERT is OFF.
=> How can I get that via script??

crö

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-10 : 09:50:50

You can only explicitly set this per table, this is not done internally, so unless you set it, it will be off.

crö
Starting Member

6 Posts

Posted - 2009-12-11 : 01:17:58

quote:
Originally posted by RickD

You can only explicitly set this per table, this is not done internally, so unless you set it, it will be off.

Thank you, RickD

uttam548
Starting Member

12 Posts

Posted - 2011-07-18 : 15:56:20

I am trying to re-insert a record in a table that has been deleted previously. Sticking with your example,this is what I am doing.
1. set identity_insert test on
2. insert into test (a,b) values (2,'this is record 2')
3. set identity_insert test off
Here (2,'this is record 2') is the record I am trying to re-insert in test table.

Let's say my current seed is 5. What will happen when Bob wants to perform this INSERT operation
INSERT INTO test VALUES ('this is record 6')
just after my query 1 gets executed? I am wondering whether this Bob's query gets executed as expected with id 6 or not. What do you think?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-07-20 : 04:22:04

You should really start your own thread as this one is 2 years old, but yes, it should be 6 as you are not re-seeding the identity.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources