Help on getting the ID from the first 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

Transact-SQL (2000)

Help on getting the ID from the first insert

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2005-07-26 : 15:01:27

I will make 2 insert transcaction to 2 tables.
ones insert will make insert to Users table and 2.nd inssert will get the UserID from the first insert and use it on second insert
like that
insert into Users values (@UserName,@Password,@Email,@Adi,@Soyadi,@DogumTarihi,@Cinsiyet,@Adres,@Semt,@Sehir,@PostaKodu,@Telefon,@CepTelefonNo,@OnayKodu,'0')

insert into KullaniciRatingleri values (UserID from the first insert,'0',3)

userID is primary key on Users table and identity(1,1)

pls help.

i tried this

insert into KullaniciRatingleri values ((select @@identity),'0',3)

but dont accpets sub queries helppp
and that is a stored procedure. 2 insert must be in same stored proc.
thx

MCP (70-229)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-26 : 15:05:24

DECLARE @UserId int

INSERT INTO Users (ColumnListGoesHere, make sure to exclude UserID from this list)
VALUES(@Password, ...)

SET @UserId = SCOPE_IDENTITY()

INSERT INTO KullaniciRatingleri (ColumnListGoesHere, include UserID this time)
VALUES (@UserID, ...)

Tara

bilencekic
Posting Yak Master

121 Posts

Posted - 2005-07-26 : 15:09:56

thx you are king :)
it worked
eheh thx much :) i wasnt using that declaring things i was uusing 2 procs for this but thx :)

MCP (70-229)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-26 : 15:13:16

You should say you are queen.

And don't use @@IDENTITY as it isn't always accurate. Use SCOPE_IDENTITY() instead. There is a SQLTeam.com article on @@IDENTITY. Go to the main page to read it.

Tara

bilencekic
Posting Yak Master

121 Posts

Posted - 2005-07-26 : 15:17:25

:) lol ok thx queen. and you should love ppl :).

MCP (70-229)

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-26 : 15:22:30

These functions also work, @@identity and IDENT_CURRENT() in addition to scope_identity()

However scope_identity() has an advantage, 'cause it works within the scope of the transaction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources