April 28, 2010 at 3:01 pm
Scott Coleman (4/28/2010)
An IDENTITY column will help you find the most recently-inserted row
Possibly it will if your inserts are serialized. If you have multiple connections with multi-statement transactions then it all depends how you define "most recent". It is quite possible to have identity values interleaved from two near-simultaneous operations for example even though one of them could commit before the other. I think it is safest to assume IDENTITY order is non-deterministic and will not necessarily match insertion order (whatever "insertion order" means) except in the special case of a single connection inserting one row at a time.
April 28, 2010 at 4:08 pm
>>A DATETIME column with DEFAULT GETDATE() will work for single-row inserts, but if multiple rows are inserted in the same statement they will all have the same value.
Here is another incorrect statement too (actually more than one failure).
1) That DEFAULT only helps if the INSERT doesn't explicitly state a value for the DATETIME column, which is certainly possible.
2) You are at the mercy of the 3.33ms precision of the DATETIME datatype. A busy system could easily have more than one concurrent insert within that interval - both will receive the same datetime value.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 29, 2010 at 7:31 am
The technical puzzle proposed in this discussion is interesting, but it has obviously run it's course. I'm interested about why you would need to know the "last" value inserted into a table that contains only a single column called [Name]. For example, are you trying to page through the list of names in an application, or perhaps you're incrementally loading data and need to know where you left off? If you describe more detail about your intended goal, then perhaps someone can offer a better solution to the actual problem.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 22, 2016 at 4:56 am
This was removed by the editor as SPAM
July 22, 2016 at 7:50 am
JasonClark (7/22/2016)
we can use SELECT @@IDENTITY, SELECT SCOPE_IDENTITY(), SELECT IDENT_CURRENT(‘TableName’) to get last inserted record: https://mostafaelmasry.com/2016/05/09/how-to-find-the-last-inserted-record-in-sql-server/[/url]
Since all of these methods depend on the table having an identity column, and the OP stated that no such column exists, none of these will work.
Also, you want to be careful about resurrecting old threads, unless you have something that will add to the discussion. The OP for this thread has not logged on since 2010, so is extremely unlikely to return to answer any questions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 12, 2019 at 6:33 am
DECLARE @MYVAR NVARCHAR(100)
DECLARE MYTESTCURSOR CURSOR
DYNAMIC
FOR
SELECT [name] FROM latest_value
OPEN MYTESTCURSOR
FETCH LAST FROM MYTESTCURSOR INTO @MYVAR
CLOSE MYTESTCURSOR
DEALLOCATE MYTESTCURSOR
SELECT @MYVAR
September 12, 2019 at 3:47 pm
DECLARE @MYVAR NVARCHAR(100)
DECLARE MYTESTCURSOR CURSOR
DYNAMIC
FOR
SELECT [name] FROM latest_value
OPEN MYTESTCURSOR
FETCH LAST FROM MYTESTCURSOR INTO @MYVAR
CLOSE MYTESTCURSOR
DEALLOCATE MYTESTCURSOR
SELECT @MYVAR
As has been stated multiple times. The table simply does not contain the information necessary to determine the "last inserted" record.
Even if the table did contain the information necessary, a CURSOR is one of the least efficient methods of retrieving said record.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply