Insert statement which return the ID of the ins. row

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)

Insert statement which return the ID of the ins. row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-08 : 17:10:10

Frank writes "If you need to get the unique ID of the row just inserted returned in the same operation as the insert itself, how would you go about doing it?

ASP EX:


Function doInsert()
 sqlStr = "INSERT INTO tab_contacts" 
 sqlStr = sqlStr & " (firstname, lastname, email)"
 sqlStr = sqlStr & "VALUE('Test', 'Person', 'this@that.com')"
 
 insertedID = doExecuteSql(sqlStr)
End Function

Function doExecuteSql(sInSql)
 //** DO ALL DIM AND SET ADODB-CONNECTION OBJECTSTUFF
 //** RUN oDBconnection.execute(sInSql) 
 //** Mysteriously get the ID of the inserted row back
 //** And return it to the calling function as so:
 doExecuteSql = mysteriouslyobtainedidvalue
End Function

It would also be of interest to be able to do multiple inserts and get a recordset of id-values returned, preferably sorted by the order of insertions.

Is this possible? maybe by creating stored procedures?

Can anyone point me down the right path?

/Frank"

ryanston
Microsoft SQL Server Product Team

89 Posts

Posted - 2005-08-08 : 17:23:18

I assume you're referring to IDENTITY column values? If so, there are several different intrinsics to do this: @@IDENTITY, IDENT_CURRENT(), and SCOPE_IDENTITY(). Each have their own semantics about scope and session limitations -- these are described in Books Online in detail. To make this work, you can write a stored procedure to do your insert, and then return back the identity value it inserted.

----------------------
Ryan Stonecipher
Developer, Microsoft SQL Server Storage Engine, DBCC
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources