ansaurus

Question

sql server: how do i get the last record that was inserted?

Answer 1

+3 A:

It's not working because your updates and your second execute are in different scopes. You may want SELECT IDENT_CURRENT('tablename')

Matthew PK 2010-08-19 23:11:50

its a good point, but i dont think its right. i put the scope identity inside the the WITH clause and still getting NULL returned

I__ 2010-08-19 23:13:43

it says that scope function requires 0 arguemnts

I__ 2010-08-19 23:15:00

No, your update is called its own execute. Then you are calling a second execute to get the identity.imho, SCOPE_IDENTITY() is most useful in stored procedures which must happen all at once.Using "with rs" doesn't mean "do all or nothing" in this regard

Matthew PK 2010-08-19 23:15:08

What version of SQL Server?

Matthew PK 2010-08-19 23:15:36

2008 sql server

I__ 2010-08-19 23:16:09

SELECT IDENT_CURRENT('tablename') will return the most recent Identity autonum from the table

Matthew PK 2010-08-19 23:16:54

you're the MAN!!!!!!!!!! thanks so much

I__ 2010-08-19 23:18:07

Best of luck to you, next time don't duplicate your question and people probably won't vote you down.

Matthew PK 2010-08-19 23:19:14

1. i dont care about my score, 2. im here to get my q answered

I__ 2010-08-19 23:21:25

Editing or updating your initial question gets it bumped. Therefore there is no reason to duplicate them. Keep in mind though that depending on your DB transaction volume this method may fail. If another record gets inserted into the DB between when you call your INSERT and when you SELECT IDENT then you will have the wrong value.To prevent this I'd suggest you use a stored procedure which returns the SCOPE_IDENTITY

Matthew PK 2010-08-19 23:24:06

@Matthew PK: sorry how do i do that?

I__ 2010-08-19 23:31:42

Stored procedures are a little outside the scope of this question, but the basic idea is that you create a procedure which accepts parameters. They can be executed just like any SQL statements "StoredProcedureName(Parameter1, Parameter2......)Then, inside the stored procedure you add all the steps required for your transaction. This ensures that everything is committed once, simultaneously and in the same scope. I try to use procedures almost at all times for DB transactions.This link may get you started: http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

Matthew PK 2010-08-19 23:36:29

Answer 2

+1 A:

IDENT_CURRENT is fine in a single user environment.

You're already on the record when you update.

.Update
lTheNewID = .Fields("ThisTableID")

lTheNewID will hold the value of the new record.

Jeff O 2010-08-20 02:09:01

i have multiusers

I__ 2010-08-20 15:31:58

Unless you're using it in a stored proc, I would avoid it.

Jeff O 2010-08-20 17:43:38

I agree with Jeff O, as stated in my answer. I'd consider using a stored procedure to ensure that all transactions occur; and the resulting ID is what you're looking for.

Matthew PK 2010-08-20 22:49:03

ansaurus

tags:

views:

answers:

sql server: how do i get the last record that was inserted?

related questions