Getting primary key from insert

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 Development (2000)

Getting primary key from insert

Author

Topic

Shakar
Starting Member

1 Post

Posted - 2002-03-12 : 16:07:19

Hi, I need to get my primary key from an insert, my primary key has an identity(1,1)

I tried the following:

SP_Insert relatedInformation input, ID output
BEGIN
INSERT (relatedInformation)
SELECT ID from table.
END

But I'm afraid that this may create havoc if I have simultanious access to this
table at the same time. ex:

A & B are inserting simultaniously:
A insert a row
B insert a row
A make a select & get B ID
B make a select & get B ID

And it will be worse if we have more than 2 insert at the same time.

I have thought of using transaction
BEGIN TRANSACTION
INSERT...
SELECT...
COMMIT TRANSACTION

But I'm not sure this is working, here is the test that we made to make sure it's working, I you have any idea how I can fix my problem, or if you can point me to somewhere where I can find a solution for it, I'd appreciate it.

The test:
I run the sp test1 first and after that I execute the following code multiple times while test1 is running, but I never seems to get the right ID.

DECLARE @vv NUMERIC

-- Modify @vv at every execution of this code
SET @vv = 111114
EXEC test2 @vv

SELECT * FROM tb_test
WHERE col2 =@vv

ALTER PROCEDURE test1
AS
BEGIN
DROP TABLE tb_test
CREATE TABLE tb_test(col1 NUMERIC IDENTITY(1,1), col2 NUMERIC)
DECLARE @i NUMERIC
SET @i=1
WHILE @i< 100000
BEGIN
BEGIN TRANSACTION
INSERT INTO tb_test(col2)
VALUES (@i)

SET @i=@i+1
COMMIT TRANSACTION
END
END

ALTER PROCEDURE test2 @v NUMERIC
AS
BEGIN
DECLARE @i NUMERIC
BEGIN TRANSACTION
INSERT INTO tb_test(col2)
VALUES (@v)
SELECT MAX(col1) FROM tb_test
SELECT @i
-- Even with a MAX it's not working
--SELECT MAX(col1) FROM tb_test
COMMIT TRANSACTION
END

Jay99

468 Posts

Posted - 2002-03-12 : 16:25:39

Look up @@IDENTITY in BOL*

*(SQL Server Books Online: reference docs that are part of your sql server installation)

Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources