selecting last 100 rows from a table.

[kotaprolus]

I have a temporary table that contain 1000 rows. I want to insert records from 900 to 1000 into another temporary table. Is ther any query that will do this in SQL server. With cursors I can do that but I need one shot query. Please give me some suggestions.

I using this query but it is not returning any rows.
select * into #t2 from #t1 t1 where not exists( select top 900 * from #t1 t2)

[Claire]

Do you have a column for identity?
If you does.

Then do the insert as

insert into TempTable select top 100 * from TableName order by ID_Col desc

[kotaprolus]

I don't have one.

[Claire]

so what is the criteria for you the pick last 100 rows?

[kotaprolus]

Order by can be any thing from original table before inserting into Temp table1. Temp table1 has no criteria. Just pick last 100 rows.

[skhanal]

Is there any unique key in the table?.

If not then you may have to use one more layer of temp table

select identity(int,1,1) as id, *
into #t3
from #t1

select a.c1,...a.cn
into #t2
from #t3 as a
where a.id < 101

[niben]

if you just want to use only one temporary table then what you could do is:

--------
insert into #t2
select top 100 * from #t1
order by id desc
---------

Here id can be any columns that you want to sort with.

I hope this helps.

Niben