get last iserted records

Question

Post reply

get last iserted records

dr_csharp

SSCommitted

Points: 1765
More actions
October 6, 2008 at 2:38 am

#77101

hello friends
whats the best way to get las X records inserted in a Table ?

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

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

if you have datetime column in your table, then select max(datetime column) from will give you the expected result.

karthik

dr_csharp SSCommitted Points: 1765 More actions · Answer 2

karthikeyan (10/6/2008)
if you have datetime column in your table, then select max(datetime column) from will give you the expected result.

no,theres no DateTime

karthik M One Orange Chip Points: 29139 More actions · Answer 3

I am sure you will get best replies from here if you post the table structure with sample data.

karthik

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

ChiragNS

One Orange Chip

Points: 26137

October 6, 2008 at 3:27 am

#881007

What about an incrementing id column?

"Keep Trying"

dr_csharp SSCommitted Points: 1765 More actions · Answer 5

Chirag (10/6/2008)
What about an incrementing id column?

i have incrementing entity column but you know ?

im going to write a procedure that call from my application each 3,4 and even per second.and in this period some records are added to Table.i need to process in last 50 records of table each time procedure call.

with this amount of process,i think its not ok to have mathematical process to count for last X records again!

if you suggest a way,ill appriciate you 🙂

dr_csharp SSCommitted Points: 1765 More actions · Answer 6

hum..sorry if i coudnt explain clearly,let me know this :

whats the best way (from performance point of view) to select last X records from a Table

dr_csharp SSCommitted Points: 1765 More actions · Answer 7

consider this table :

CREATE TABLE [dbo].[Customer] (

[Id] [int] IDENTITY (1, 1) NOT NULL ,

[Code] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Family] [nvarchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

how can i get last Id from Customer Table in my procedure?

(i dont insert into Customer table in my procedure,i just need last id in my procedure)

thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

DECLARE @MaxID INT

SELECT @MaxID = Max(ID) FROM Customer

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 9

GilaMonster (10/6/2008)
DECLARE @MaxID INT
SELECT @MaxID = Max(ID) FROM Customer

which one is better :

i save @@Identity in a field when record insert in Table and i read saved id in my procedure instead to use select max(ID)..

because of performance

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

@@identity will get you the identity value that you just inserted. MAX(ID) will get you the last value inserted. If multiple users cn be inserting at the same time, the two values may not be the same.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 11

GilaMonster (10/6/2008)
@@identity will get you the identity value that you just inserted. MAX(ID) will get you the last value inserted. If multiple users cn be inserting at the same time, the two values may not be the same.

i know,i think i didnt ask clearly !let ask again :

i have a customer table and two procedure :

1-procedureInsert

2-procedureAnalyze

i mean when user Insert record in customer table we can save last id (by using @@Identity or Scope_Identity ) in a field(when use procedureInsert )

and when we wanna analyze inserted records,we use procedureAnalyze and instead of use select max(id) in procedureAnalyze we van use the field that stored by procedureInsert before.

now,isnt this way better ?

Sanaullah SSC Eights! Points: 892 More actions · Answer 12

Hi,

AS the values are changing every second the in procedure we should use

WITH RECOMPILE, So wheen ever it is called it recompiles the query and get the last 50 records no doubt what is the last one.

Regards,

Syed Sanaullah Khadri.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

dr_csharp (10/6/2008)
GilaMonster (10/6/2008)
DECLARE @MaxID INT
SELECT @MaxID = Max(ID) FROM Customer
which one is better :
i save @@Identity in a field when record insert in Table and i read saved id in my procedure instead to use select max(ID)..
because of performance

My recommendation is to NEVER EVER use @@Identity because if you someday put an audit trigger on the table, @@Identity could return the wrong values. Use SCOPE_IDENTITY() instead.

--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)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

Jeff Moden (10/7/2008)

karthikeyan (10/6/2008)
if you have datetime column in your table, then select max(datetime column) from will give you the expected result.

No... if someone happens to do an Insert at the same time, you may get the wrong rows. Don't ever do it this way. And don't even think of using a transaction with and "Insert/Select" to do it... guaranteed recipe for more deadlocks than you can shake a stick at.

EDIT... let me make a correction now that I've "read down" in the thread a bit. You can do it that way if you want the last 50 rows... just keep in mind that what you have as the "last 50 rows" could change in a heartbeat.

--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)