getting id of last row inserted

I saw this code recently,"select growerid from growers_temp order by growerid
desc", as a way to get the id of the last row inserted. What is the downside
to this method?
I've also seen these methods:
strSQL = "insert into tblVacHead
(emp_first,emp_last,emp_login,dateofhire,active) " & _
"values ('"&strFirst&"','"&strLast&"','" _
&strLogin&"','"&dteHired&"', 'Y')"
connVacation.Execute strSQL
' get the record id of the new record we just added
' we'll use this to add a new record to the detail table
strSQL = "Select IDENT_CURRENT('tblVacHead') AS idNewRecord"
set rsID = connVacation.execute(strSQL)
-- another way
DECLARE @.MyUniqueidentifier uniqueidentifier
SET @.MyUniqueidentifier = NEWID()
******
INSERT INTO [myTable] ([myField], [MyUniqueidentifier])
VALUES(@.myField, @.MyUniqueidentifier)
SELECT @.MyUniqueidentifier
*****
INSERT tblTableName (field1, field2, field3) VALUES (value1, value2, value3);
SELECT @.@.Identity as intID
*****
Is one of these better than the other or is there another "better" way?
Thanks,
--
Dan D.As further explanation, I'm working with an asp application so there could be
many users inserting into the same table at virtually the same time. I'm
wondering if scope_identity() would be the best choice as in:
Insert into a (a_desc, b_desc) Values ('aaa', 'bbb')
Select SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
Would this make sure that the user who just inserted into the table gets the
id back for the row he inserted rather than a row that someone else inserted?
Thanks,
Dan D.
"Dan D." wrote:
> I saw this code recently,"select growerid from growers_temp order by growerid
> desc", as a way to get the id of the last row inserted. What is the downside
> to this method?
> I've also seen these methods:
> strSQL = "insert into tblVacHead
> (emp_first,emp_last,emp_login,dateofhire,active) " & _
> "values ('"&strFirst&"','"&strLast&"','" _
> &strLogin&"','"&dteHired&"', 'Y')"
> connVacation.Execute strSQL
> ' get the record id of the new record we just added
> ' we'll use this to add a new record to the detail table
> strSQL = "Select IDENT_CURRENT('tblVacHead') AS idNewRecord"
> set rsID = connVacation.execute(strSQL)
>
> -- another way
> DECLARE @.MyUniqueidentifier uniqueidentifier
> SET @.MyUniqueidentifier = NEWID()
> ******
> INSERT INTO [myTable] ([myField], [MyUniqueidentifier])
> VALUES(@.myField, @.MyUniqueidentifier)
> SELECT @.MyUniqueidentifier
> *****
> INSERT tblTableName (field1, field2, field3) VALUES (value1, value2, value3);
> SELECT @.@.Identity as intID
> *****
> Is one of these better than the other or is there another "better" way?
> Thanks,
> --
> Dan D.|||Use SCOPE_IDENTITY() or @.@.IDENTITY and return the result as an output
parameter from the stored procedure that does the insert. These
functions have local scope so other users inserting won't cause a
conflict. IDENT_CURRENT is different because it is table-scoped and so
you will see other users' inserts.
--
David Portas
SQL Server MVP
--|||Hi,
You could also use IDEN_CURRENT
Returns the last identity value generated for a specified table in any
session and any scope.
Usage:-
Select IDENT_CURRENT('table_name')
Note:-
SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions in that
they return values inserted into IDENTITY columns. But I preffer to use
IDENT_CURRENT being it is limited to a specific table.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:7DFD6016-2194-4899-94DD-0A82E4B4F40E@.microsoft.com...
> As further explanation, I'm working with an asp application so there could
> be
> many users inserting into the same table at virtually the same time. I'm
> wondering if scope_identity() would be the best choice as in:
> Insert into a (a_desc, b_desc) Values ('aaa', 'bbb')
> Select SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
> Would this make sure that the user who just inserted into the table gets
> the
> id back for the row he inserted rather than a row that someone else
> inserted?
> Thanks,
>
> --
> Dan D.
>
> "Dan D." wrote:
>> I saw this code recently,"select growerid from growers_temp order by
>> growerid
>> desc", as a way to get the id of the last row inserted. What is the
>> downside
>> to this method?
>> I've also seen these methods:
>> strSQL = "insert into tblVacHead
>> (emp_first,emp_last,emp_login,dateofhire,active) " & _
>> "values ('"&strFirst&"','"&strLast&"','" _
>> &strLogin&"','"&dteHired&"', 'Y')"
>> connVacation.Execute strSQL
>> ' get the record id of the new record we just added
>> ' we'll use this to add a new record to the detail table
>> strSQL = "Select IDENT_CURRENT('tblVacHead') AS idNewRecord"
>> set rsID = connVacation.execute(strSQL)
>>
>> -- another way
>> DECLARE @.MyUniqueidentifier uniqueidentifier
>> SET @.MyUniqueidentifier = NEWID()
>> ******
>> INSERT INTO [myTable] ([myField], [MyUniqueidentifier])
>> VALUES(@.myField, @.MyUniqueidentifier)
>> SELECT @.MyUniqueidentifier
>> *****
>> INSERT tblTableName (field1, field2, field3) VALUES (value1, value2,
>> value3);
>> SELECT @.@.Identity as intID
>> *****
>> Is one of these better than the other or is there another "better" way?
>> Thanks,
>> --
>> Dan D.|||Thanks. That's what I'm looking for then.
Will it still work the same way if we're passing the query string in rather
than using a stored procedure?
--
Dan D.
"David Portas" wrote:
> Use SCOPE_IDENTITY() or @.@.IDENTITY and return the result as an output
> parameter from the stored procedure that does the insert. These
> functions have local scope so other users inserting won't cause a
> conflict. IDENT_CURRENT is different because it is table-scoped and so
> you will see other users' inserts.
> --
> David Portas
> SQL Server MVP
> --
>|||Is there no scope involved with using "select growerid from growers_temp
order by growerid desc"? So, the user could get anyone's id?
Thanks,
--
Dan D.
"David Portas" wrote:
> Use SCOPE_IDENTITY() or @.@.IDENTITY and return the result as an output
> parameter from the stored procedure that does the insert. These
> functions have local scope so other users inserting won't cause a
> conflict. IDENT_CURRENT is different because it is table-scoped and so
> you will see other users' inserts.
> --
> David Portas
> SQL Server MVP
> --
>|||Why would you use a dynamic query string for a single row insert?
Always use a stored procedure unless you have a good reason not to.
@.@.IDENTITY and SCOPE_IDENTITY can be passed in a dynamic string and
will remain scoped within the connection. Put them in the same SQL
batch as the INSERT. If you don't do that then you won't be able to use
SCOPE_IDENTITY because it is also scoped to the batch. @.@.IDENTITY on
the other hand can be issued in a separate batch and see a previous
INSERT but it will also see any IDENTITY value inserted by a trigger on
the table, which probably isn't what you want. Also, if you issue more
than one SQL statement and you are using connection pooling there may
be a risk that @.@.IDENTITY sees another person's insert ... really the
whole thing is a lot simpler if you use a stored proc - that's what
they exist for.
--
David Portas
SQL Server MVP
--|||Exactly
--
David Portas
SQL Server MVP
--|||Thanks.
--
Dan D.
"David Portas" wrote:
> Why would you use a dynamic query string for a single row insert?
> Always use a stored procedure unless you have a good reason not to.
> @.@.IDENTITY and SCOPE_IDENTITY can be passed in a dynamic string and
> will remain scoped within the connection. Put them in the same SQL
> batch as the INSERT. If you don't do that then you won't be able to use
> SCOPE_IDENTITY because it is also scoped to the batch. @.@.IDENTITY on
> the other hand can be issued in a separate batch and see a previous
> INSERT but it will also see any IDENTITY value inserted by a trigger on
> the table, which probably isn't what you want. Also, if you issue more
> than one SQL statement and you are using connection pooling there may
> be a risk that @.@.IDENTITY sees another person's insert ... really the
> whole thing is a lot simpler if you use a stored proc - that's what
> they exist for.
> --
> David Portas
> SQL Server MVP
> --
>|||I just reread your post. Can you give me an example of "@.@.IDENTITY and
SCOPE_IDENTITY can be passed in a dynamic string and will remain scoped
within the connection. Put them in the same SQL batch as the INSERT"?
How do you put them in the same batch and send them at the same time?
Thanks,
--
Dan D.
"David Portas" wrote:
> Why would you use a dynamic query string for a single row insert?
> Always use a stored procedure unless you have a good reason not to.
> @.@.IDENTITY and SCOPE_IDENTITY can be passed in a dynamic string and
> will remain scoped within the connection. Put them in the same SQL
> batch as the INSERT. If you don't do that then you won't be able to use
> SCOPE_IDENTITY because it is also scoped to the batch. @.@.IDENTITY on
> the other hand can be issued in a separate batch and see a previous
> INSERT but it will also see any IDENTITY value inserted by a trigger on
> the table, which probably isn't what you want. Also, if you issue more
> than one SQL statement and you are using connection pooling there may
> be a risk that @.@.IDENTITY sees another person's insert ... really the
> whole thing is a lot simpler if you use a stored proc - that's what
> they exist for.
> --
> David Portas
> SQL Server MVP
> --
>|||"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A399997F-B379-40DC-A955-12D3821DC416@.microsoft.com...
>I just reread your post. Can you give me an example of "@.@.IDENTITY and
> SCOPE_IDENTITY can be passed in a dynamic string and will remain scoped
> within the connection. Put them in the same SQL batch as the INSERT"?
> How do you put them in the same batch and send them at the same time?
>
Here's the batch
insert into t(a,b,c) values(@.a,@.b,@.c)
set @.id = SCOPE_IDENTITY()
Then you need to bind 3 input parameters and one output parameter. You can
do this in your client library or from TSQL using sp_executesql
sp_executesql
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q262499
David|||Thanks.
--
Dan D.
"David Browne" wrote:
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A399997F-B379-40DC-A955-12D3821DC416@.microsoft.com...
> >I just reread your post. Can you give me an example of "@.@.IDENTITY and
> > SCOPE_IDENTITY can be passed in a dynamic string and will remain scoped
> > within the connection. Put them in the same SQL batch as the INSERT"?
> >
> > How do you put them in the same batch and send them at the same time?
> >
> Here's the batch
> insert into t(a,b,c) values(@.a,@.b,@.c)
> set @.id = SCOPE_IDENTITY()
>
> Then you need to bind 3 input parameters and one output parameter. You can
> do this in your client library or from TSQL using sp_executesql
> sp_executesql
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
>
> INF: Using Output Parameters with sp_executesql
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;q262499
> David
>
>