@@identity problem 2

[mimi2]

hello , my variable @@identity increases by it self ! could you tell me what's wrong with my code. thanks.
INSERT INTO table1(aa,bb)VALUES (@aa,@bb)
select @VarID = @@IDENTITY

INSERT INTO table2(cc,dd) VALUES (@@IDENTITY, @dd)
or
INSERT INTO table2(cc,dd) VALUES (@VarID , @dd)

does not give me the last autoincrement number that has been generated in table1.
how can i hold on that value ?
thanks

 

[tonyjenks]

A possible cause of this is because the @@identity is a global variable and it can be incremented by other inserts on the server. You could use the function IDENT_CURRENT('<table name>') which will limit the scope to a particular table

 

[mimi2]

thanks for your answer.
i tried set @VarID = IDENT_CURRENT('table1')
but it does not work.

 

[mimi2]

i even tried some thing like:
declare @x integer
set @x = (select max(RecordID) from Table1)

 

[Sqlsa]

Hi mimi,
use the following code and create a SP..
execute it..you will see that..
---------------
CREATE PROCEDURE [Tester] AS
--Test table has two columns by name columns aa, bb
--aa is identity column
--bb is char(10) ..can be anything..this is for test
declare @latest int

insert into test(bb) values('abcd')

select @latest=@@identity

print @latest

select * from test
--------------------
No you will see that @aa and the latest Identity value in the table will be same.

You just don't include the identity column name in the column list for insert and don't include the value for the identity column in Values List of INSERT statement. Once a column is specified as IDENTITY column , SQL server will take care of it.


There is a lot about IDENTITY in BOL.
Hope this helps.

Sreenivas
avnsr@hotmail.com
-----------------

 

Hi mimi,

There is a small correction. please ignore the above code and try this...

use the following code and create a SP..
execute it..you will see that..

--Test table has two columns by name columns aa, bb
--aa is identity column
--bb is char(10) ..can be anything..this is for test


---------------
CREATE PROCEDURE [Tester] AS
declare @latest int

insert into test(bb) values('abcd')

select @latest=@@identity

print @latest

select * from test
--------------------
No you will see that @latest and the latest Identity value in the table will be same.

You just don't include the IDENTITY COLUMN name in the COLUMNS LIST for INSERT statement and don't include the value for the IDENTITY COLUMN in VALUES LIST of INSERT statement. Once a column is specified as IDENTITY column , SQL server will take care of it.


There is a lot about IDENTITY in BOL.
Hope this helps.

Sreenivas
avnsr@hotmail.com
-----------------

 

[mimi2]

thanks. finally
set @x = (select max(RecordID) from Table1) worked the 100th time i tried it!!!

 

[tarrike]

I think your DBA will probably want to see something like

set @x = (SELECT @@IDENTITY )

The overhead on this is almost zero.

The

set @x = (select max(RecordID) from Table1)

if you have a very large table, or a large number of records, will get quite timely, but will be just as effective on small tables.

Set show execution plan in query analizer, and look at the difference.

Tom )

 

[tlbroadbent]

While I agree that you should use an identity function to identify the last identity inserted, I would state that SQL Server handles Select Max(RecID) From Table very efficiently if there is an Index on RecID. SQL Server will search backward in the index and find the Max(RecID) on the first record it searches.

Mimi,

Is there an identity column on the table? If so, don't insert any values into it. The insert will fail. Identities are auto incrementing columns.

If you use SQL 2000 I recommend using the Scope_Identity() function to get the identity value. This will guarantee that you get the correct value.

SELECT @val = SCOPE_IDENTITY()

If RecID is not an identity column but is incremented by your code then use Select @val=max(RecID) From Table to determine the latest value. Make sure the column is indexed or as mentioned in an earlier post, performance will suffer. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.