Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
hapetter
Starting Member
10 Posts |
Posted - 2013-06-13 : 07:19:32
|
Hi.This is my application.I add 120 rows of data when an event occurs (every sec) using Union ALL insert . Then I check my row cound by a select statement. Then I like to remove the oldest (120) rows if rowcount is above a limit (ex. 10000 rows). Hence it will work as a FIFO.So my question is: Is it possible to use row index number for creating the delete rows query: something like: 'Select * From eTable Where "rowIndex < 120'Can row index be used? Will row index update after deleting rows so that the oldest row always starts at row index 0?ThanksRegardsHans |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-13 : 07:35:01
|
Nope Rowindex doesnt automatically change upon deleteyou need to use OFFSET..FETCH syntax as ROW_NUMBER is not available in SQL Server Compactie something likeIF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TempTable') DROP TABLE TempTableSELECT * INTO TempTableFROM YourTableORDER BY RowIndexOFFSET 0 ROWSFETCH FIRST 120 ROWSDELETE tFROM YourTable tJOIN TempTable tmpON tmp.RowIndex = t.RowIndex ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
hapetter
Starting Member
10 Posts |
Posted - 2013-06-13 : 08:05:26
|
In other words, how to delete the oldest 120 rows in a table without knowing/caring about the column contentRegardsHans |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 00:42:37
|
quote: Originally posted by hapetter In other words, how to delete the oldest 120 rows in a table without knowing/caring about the column contentRegardsHans
See my suggestion aboveyou'll have to specify "oldest" in terms of one or more columns. There's no concept of old and new in sql table otherwise. My given solutions finds it based on your RowIndex column values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|