How To Get Last Inserted Record Value In SQL Server

Question

Post reply

How To Get Last Inserted Record Value In SQL Server

Vijay Sinha

Mr or Mrs. 500

Points: 591
More actions
April 25, 2010 at 11:47 pm

#139882

How To Get Last Inserted Record Value In SQL Server
The table have no Primary key, identity, it have only one column "Name"(Column Name)

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

sandy_1006 SSC Eights! Points: 903 More actions · Answer 1

Hi,

SELECT *

FROM TABLE

WHERE ID = (SELECT MAX(ID) FROM TABLE)

What does your table structure look like? Do you have a naturally

increasing column (DATETIME reflecting when the row was

created)?

Regards

Sandeep

Vijay Sinha Mr or Mrs. 500 Points: 591 More actions · Answer 2

Thanks so much for your replies!!

But, my table have only one column, the table structure look like --

Create table latest_value

(

[name] varchar(50)

)

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi Vijay,

The concern is, unless you have any one of the column like identity, date of creating/modify, or row version (timestamp), it’s very difficult/can’t to get the last insert row from the table.

And have the question; you have any audit for that table?

homebrew01 SSC Guru Points: 55539 More actions · Answer 4

You could create an insert trigger that puts the value NAME into another table. It would overwrite whatever the previous value was with the new value. Or add a new unique column to your table.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

If your table lacks a key then obviously you ought to add one at the first opportunity, otherwise it could be difficult to make any sense of the results. Why would you want duplicate rows in the table?

There is not necessarily any single latest row because multiple rows could have been inserted simultaneously.

In SQL Server 2008 and later you can turn on the Change Data Capture feature so that you can retrieve information about the row insertion order. In earlier versions that information is not exposed unless you record it in the database yourself.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

Vijay Sinha (4/25/2010)
How To Get Last Inserted Record Value In SQL Server
The table have no Primary key, identity, it have only one column "Name"(Column Name)

Without either a date column or and identity column, it can't be done reliably. As David suggests, you should probably add one, the other, or perhaps both.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

maven.avi SSC Veteran Points: 259 More actions · Answer 7

As per your question, i have the written the following query.

Hope it helps.

Create table latest_value

(

[name] varchar(50)

)

go

Insert into latest_value

Select 'Avinash'

Union All

Select 'Suresh'

Union All

select 'Rahul'

Union All

Select 'Anil'

Union All

Select 'Zayed'

Union All

Select 'Suresh'

go

Select * from latest_value

go

Select identity(int, 1, 1) as idenCol,Name

into #temptbl

from latest_value

go

select top 1 Name from #temptbl

order by idenCol desc

go

drop table #temptbl

go

drop table latest_value

-- Regards,

Avinash

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

maven.avi (4/27/2010)
As per your question, i have the written the following query.
Hope it helps.
Create table latest_value
(
[name] varchar(50)
)
go
Insert into latest_value
Select 'Avinash'
Union All
Select 'Suresh'
Union All
select 'Rahul'
Union All
Select 'Anil'
Union All
Select 'Zayed'
Union All
Select 'Suresh'
go
Select * from latest_value
go
Select identity(int, 1, 1) as idenCol,Name
into #temptbl
from latest_value
go
select top 1 Name from #temptbl
order by idenCol desc
go
drop table #temptbl
go
drop table latest_value
-- Regards,
Avinash

That does NOT get you what you want, which is the last row inserted into the main production table. That gets you the 'last' row inserted into the TEMPORARY table. Tables are UNORDERED sets of data unless they have a CLUSTERED INDEX on them. SQL Server can insert the rows from the production table into the temp table in ANY ORDER IT PLEASES, and that order can change from execution to execution. You cannot rely on this method.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Eric M Russell SSC Guru Points: 125519 More actions · Answer 9

The last value inserted into your table was 'Snoopy'. Prove me wrong.

Seriously, without a date/time column, or at least a sequential identifier, there is no deterministic way to know which value was inserted last. Still there are ways to have a clue.

For example, you could query the default trace for the last INSERT on the table, but that won't work if the table is BULK inserted.

http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

Also, you could also create an AFTER INSERT trigger on the table which updates the inserted value in another table each time an insert is performed, but again that won't work for BULK inserts.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 10

TheSQLGuru (4/27/2010)
Tables are UNORDERED sets of data unless they have a CLUSTERED INDEX on them.

Tables are unordered sets even if they do have a clustered index. A clustered index is just one form of internal storage. It doesn't impose any logical ordering any more than a nonclustered index on a heap does.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

David Portas (4/27/2010)
TheSQLGuru (4/27/2010)
Tables are UNORDERED sets of data unless they have a CLUSTERED INDEX on them.
Tables are unordered sets even if they do have a clustered index. A clustered index is just one form of internal storage. It doesn't impose any logical ordering any more than a nonclustered index on a heap does.

Heh... obviously not a fan of WITH (INDEX(0)) or the "Quirky" Update, huh?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 12

Jeff Moden (4/27/2010)
Heh... obviously not a fan of WITH (INDEX(0)) or the "Quirky" Update, huh?

WITH (INDEX(0)) doesn't mean a table is ordered. It just specifies a hint for an execution plan. In fact it doesn't even order the query results AFAIK because the INDEX hint without ORDER BY results in an unordered scan. Rows might be returned in allocation order rather than cluster key order or they might be reordered by a merry-go-round scan.

vidya_pande SSCertifiable Points: 5275 More actions · Answer 13

The order in which records are inserted does not hold any significance unless you specifically track the insert time details.

Also the order in which records are stored does not hold any significance in the table if you do not have clustered index or auto increment or ordered data.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 14

Just to make sure we completely cover the topic:

An IDENTITY column will help you find the most recently-inserted row, unless IDENTITY_INSERT or DBCC CHECKIDENT (RESEED) are used to insert records out of order.

A TIMESTAMP column will allow you to find the row used in the most recent INSERT or UPDATE.

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.