Does 'INSERT' return a value when used with SQL Server? 2

[skurpyun]

Question for all and anyone who would like to help.
I have a database table with a primary key field called 'ID' which is auto-incremented (i.e. has identity set to yes and an identity seed of 1, increment 1). Other fields in the database are 'Title' and 'Location'.

When I do an INSERT (to insert a new Title and Location), I would like to be able to get the value of the ID field for this new INSERT. So my question is, does an INSERT statement return anything? and if it does, would that be the 'ID' field in this case?

Any help would be appreciated. I had seen a similar post on here a while back, but after doing some searches through the post i was unsuccessful at finding it.

 

[swampBoogie]

The insert statement does not return anything. You need to do a select in order to get the auto_increment value. Look for @@identity, scope_identity in BOL (Books on Line).

 

[skurpyun]

ok thanks swampBoogie, I'll look into that. I guess i had gotten too used to using Oracle over the past few months. I was almost certain the insert statement would return something. thanks again.

 

[Discord]

you could create an INSERT trigger that returns the newly created identity value.
SELECT IdentityField FROM Inserted

 

[sguslan]

In any database Oracle or otherwise, the return would be the SQL State, which happens to be 0 in case of success.

If for instance you were using SQL Server Query Analyzer, then if you execute the following statement

UPDATE authors SET au_lname = 'Jones'
WHERE au_id = '999-888-7777'

then @@ROWCOUNT would equal 0 if no rows were updated and the number of rows if successful.

So it depends on the tools you are using and whether they expose the number of rows affected by a query or other SQL statements.

Another valuable variable in Query Analyzer that helps in this respect is @@ERROR. It is set to 0 if the statement executed successfully. If an error occurs, an error message is returned. @@ERROR returns the number of the error message until another Transact-SQL statement is executed. You can view the text associated with an @@ERROR error number in the sysmessages system table.

Hope i was helpful ...

 

[sguslan]

Sorry for the wrong posting as it was meant for another thread. Pardon my ignorance ...

 

[SQLSister]

Discord, it is not recommended to put select statements in triggers. Skuryun, I would not use just ID as the column name, sooner or later you will want to distinguish it from other ids and it gets confusing without a more descriptive name. For instance in my database I have AirportId, profileId, RestaurantID, HotelID, RunwayID, etc probaly several hundred of them all together, many of which are foreign keys in other tables. If I had named each ID then it woudl get mucvh more confusing to write the joins and program code because it would be hard to remember which one I wanted to refer to.

 

[skurpyun]

SQLSister: thanks for the note. I understand what you are saying about using more descriptive "ID's". I actually am using a descriptive id in the real life case, i just said id here for simplicity. But I totally agree with what you are saying....and this is based on months of banging my head against my desk for making that exact mistake!!

Sguslan: your response was actually helpful. You got the right thread, either by choice or by accident.

Discord: i took your answer and applied it. will test it in a few though, as a more critical issue on another project has come up for me to look into. But basically this is what i'm doing:
set rs = c.execute("[INSERT STATEMENT HERE];SELECT @@identity as Ident;&quot

I'll see if it works in a few minutes.
Thanks to all of you.
This site continues to prove to be an amazing resource for me!!

 

[skurpyun]

Quick update:
Using the following logic worked:

set rs = c.execute("[INSERT STATEMENT HERE];SELECT @@identity as Ident;&quot

The catch in making it work is that after the query runs, in order to get the Ident value, you need to first move to the next recordset like this: rs = rs.nextrecordset.

So in all i end up with code like this:

set rs = c.execute("[INSERT STATEMENT HERE];SELECT @@identity as Ident;&quot

rs = rs.nextRecordset
newId = rs("Ident&quot

And newId holds the primary key value for the new insert.
thanks swampBoogie.
Thanks folks......now....on to the next hurdle.....

 

[ETN]

I'm afraid your answer can cause somes problems. Let say 2 users are at the same page at the same moment. This way of programming may cause an invalid newID field because the nextRecordset may be the recordSet of the other user.

i.e. :
user1 : execute insert
user2: execute insert
user1: rs = rs.nextRecordset
user2: rs = rs.nextRecordset
user1: newId = rs("Ident&quot
user2: newId = rs("Ident&quot

I am maybe mistaking but i would be afraid in this case if 2 users are at the exact same moment (which must be rare but might happen) that user1 would get the ident of user2.

 

[ETN]

OK. i'm sorry i've read the thread: thread183-672637
--
@@IDENTITY will only return the last inserted identity value from the current session (connection). You won't pick up another user's value.
--

Sorry !

 

[skurpyun]

ETN,
yes i understand your initial point. I was concerned with this also when i was first going through the code. But then a co-worker mentioned that the returned @@Identity is per connection and not database wide. In other words, she was saying basically the same thing "Thread183-672637" says.
Thanks though.

-skurpyun

 

[MuadDubby]

I just wanted to send you all a HUGE THANK YOU for this post. The Hair Club for men won't be making a customer out of me ant time soon!

.DaviD.