Restrict The Number Of Records From Query

Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?Originally posted by baburajv
Hi,

ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query

i want to implement a query system which will give me the first 50 records, next 50 ,... and so on

can ne body help ?

select top 50 from table_name|||SELECT TOP 50 FROM TABLE_NAME

WILL RETURN THE TOP 50 ROWS FROM THE TABLE

HOW CAN I RETRIEVE THE NEXT 50 ROWS|||Originally posted by baburajv
SELECT TOP 50 FROM TABLE_NAME

WILL RETURN THE TOP 50 ROWS FROM THE TABLE

HOW CAN I RETRIEVE THE NEXT 50 ROWS

I think then u need to use temperory tables for it.|||select top NUMBER_OF_ROWS *
from table
where column NOT IN
(select top BEGIN_AT column
from table)

restrict query results

Hi guys, say i wrote a query that returns 1,000 records.. what kinda query could i write that only returns say the first 50 records of the 1,000 recs..

SELECT TOP 50 * FROM yourTable

|||thanks alot, another question.. what if i wanted the result 100 to 150?|||the way I phrased that question was kinda ambigous.. i'll put it in a clearer way. say if out of that 1,000 results i wanted record 100 to record 150 basically skipping the first 99 records and returning those 50 results in the middle, how could i code that in sql?|||With SQLPaging As
(
Select Row_number() Over (Order by MyField) as RunningNumber,*
From MyTable
)
Select * from SQLPaging
Where RunningNumber between 100 and 150

Restoring records in a particular table

Hi,
I have unknowingly deleted 100 records instead of 1 record.
I haven't set autocommit off also...
Could you please let me know if there is any way to get back those records...
Thanks in advance...
Did you take a backup prior to doing the delete?
Last night or something?
You could restore it with a different DB name and import the records...
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...
|||If you are using the full recovery model and have been doing database and
transaction log dumps you should be able to restore the database to a point
in time before you deleted these rows.
You can also look at tools like Log Explorer which may help.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...
|||There is back up, but i dont know the duration of the backup they take
by 10:30 i have deleted the records from particular table.
See the query
delete tp_no='3' from tablename ,without adding the condition so that
multiple records are deleted with the tp_no='3'
"Warren Brunk" wrote:

> Did you take a backup prior to doing the delete?
> Last night or something?
> You could restore it with a different DB name and import the records...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Srik" <Srik@.discussions.microsoft.com> wrote in message
> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
>
>
|||Back up your transaction log, and do a point in time recovery.
Or you can take your latest full back up and restore it to a new DB and copy
the data in.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:0E12A098-1D1B-4E6C-9BA1-853FC2F7ACA2@.microsoft.com...[vbcol=seagreen]
> There is back up, but i dont know the duration of the backup they take
> by 10:30 i have deleted the records from particular table.
> See the query
> delete tp_no='3' from tablename ,without adding the condition so that
> multiple records are deleted with the tp_no='3'
> "Warren Brunk" wrote:
|||If you dont have backup or database snapshot where you can read the data you
deleted, perhaps you can use some third party tools for transaction log
managment. I believe red gate has one tool as well as lumigent (log
explorer).
There are probably others, if you search on the net you should find a
couple.
MC
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
records...
> Thanks in advance...

Restoring records in a particular table

Hi,
I have unknowingly deleted 100 records instead of 1 record.
I haven't set autocommit off also...
Could you please let me know if there is any way to get back those records...
Thanks in advance...Did you take a backup prior to doing the delete?
Last night or something?
You could restore it with a different DB name and import the records...
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||If you are using the full recovery model and have been doing database and
transaction log dumps you should be able to restore the database to a point
in time before you deleted these rows.
You can also look at tools like Log Explorer which may help.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||There is back up, but i dont know the duration of the backup they take
by 10:30 i have deleted the records from particular table.
See the query
delete tp_no='3' from tablename ,without adding the condition so that
multiple records are deleted with the tp_no='3'
"Warren Brunk" wrote:
> Did you take a backup prior to doing the delete?
> Last night or something?
> You could restore it with a different DB name and import the records...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Srik" <Srik@.discussions.microsoft.com> wrote in message
> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> > Hi,
> >
> > I have unknowingly deleted 100 records instead of 1 record.
> > I haven't set autocommit off also...
> > Could you please let me know if there is any way to get back those
> > records...
> >
> > Thanks in advance...
>
>|||Back up your transaction log, and do a point in time recovery.
Or you can take your latest full back up and restore it to a new DB and copy
the data in.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:0E12A098-1D1B-4E6C-9BA1-853FC2F7ACA2@.microsoft.com...
> There is back up, but i dont know the duration of the backup they take
> by 10:30 i have deleted the records from particular table.
> See the query
> delete tp_no='3' from tablename ,without adding the condition so that
> multiple records are deleted with the tp_no='3'
> "Warren Brunk" wrote:
>> Did you take a backup prior to doing the delete?
>> Last night or something?
>> You could restore it with a different DB name and import the records...
>> thanks,
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "Srik" <Srik@.discussions.microsoft.com> wrote in message
>> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
>> > Hi,
>> >
>> > I have unknowingly deleted 100 records instead of 1 record.
>> > I haven't set autocommit off also...
>> > Could you please let me know if there is any way to get back those
>> > records...
>> >
>> > Thanks in advance...
>>|||If you dont have backup or database snapshot where you can read the data you
deleted, perhaps you can use some third party tools for transaction log
managment. I believe red gate has one tool as well as lumigent (log
explorer).
There are probably others, if you search on the net you should find a
couple.
MC
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
records...
> Thanks in advance...

Restoring records in a particular table

Hi,
I have unknowingly deleted 100 records instead of 1 record.
I haven't set autocommit off also...
Could you please let me know if there is any way to get back those records..
.
Thanks in advance...Did you take a backup prior to doing the delete?
Last night or something?
You could restore it with a different DB name and import the records...
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||If you are using the full recovery model and have been doing database and
transaction log dumps you should be able to restore the database to a point
in time before you deleted these rows.
You can also look at tools like Log Explorer which may help.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||There is back up, but i dont know the duration of the backup they take
by 10:30 i have deleted the records from particular table.
See the query
delete tp_no='3' from tablename ,without adding the condition so that
multiple records are deleted with the tp_no='3'
"Warren Brunk" wrote:

> Did you take a backup prior to doing the delete?
> Last night or something?
> You could restore it with a different DB name and import the records...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Srik" <Srik@.discussions.microsoft.com> wrote in message
> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
>
>|||Back up your transaction log, and do a point in time recovery.
Or you can take your latest full back up and restore it to a new DB and copy
the data in.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:0E12A098-1D1B-4E6C-9BA1-853FC2F7ACA2@.microsoft.com...[vbcol=seagreen]
> There is back up, but i dont know the duration of the backup they take
> by 10:30 i have deleted the records from particular table.
> See the query
> delete tp_no='3' from tablename ,without adding the condition so that
> multiple records are deleted with the tp_no='3'
> "Warren Brunk" wrote:
>|||If you dont have backup or database snapshot where you can read the data you
deleted, perhaps you can use some third party tools for transaction log
managment. I believe red gate has one tool as well as lumigent (log
explorer).
There are probably others, if you search on the net you should find a
couple.
MC
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
records...
> Thanks in advance...

Restoring deleted records with out of row blobs?

I have a database that had a number of rows in multiple tables deleted -
fortunately there was very little activity after these deletions (mostly more
deletions a few days later).
I have the log files, but the original inserts just have the pointer to the
blob. I tried generating undo scripts with apexsql Log but it doesn't even
reference the blob column when the script generates (restoring from a
detached mdf does give the pointer, but that pointer doesn't go anywhere and
a select on it is blank/null).
How is one supposed to go about restoring records / rolling back a
transaction log for deleting a row with an out-of-row blob? Am I totally
screwed even though nothing else happened in the database aside from the
deletes before the backup was taken?
"Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>I have a database that had a number of rows in multiple tables deleted -
> fortunately there was very little activity after these deletions (mostly
> more
> deletions a few days later).
> I have the log files, but the original inserts just have the pointer to
> the
> blob. I tried generating undo scripts with apexsql Log but it doesn't
> even
> reference the blob column when the script generates (restoring from a
> detached mdf does give the pointer, but that pointer doesn't go anywhere
> and
> a select on it is blank/null).
> How is one supposed to go about restoring records / rolling back a
> transaction log for deleting a row with an out-of-row blob? Am I totally
> screwed even though nothing else happened in the database aside from the
> deletes before the backup was taken?
Use the last full backup and the subsequent log backups to do a
point-in-time restore to a new database. Then do INSERTS from the restored
database to your real database.
David
|||"David Browne" wrote:

> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restored
> database to your real database.
> David
>
Unfortunately the database was created between full backups and the only
remaining full backup is after the fact. There's no way to roll back the
transactions from the ldf?
How are blobs handled in the transaction log? Is it just a limitation of
the log viewer that I'm using that I don't see the the anything about the
content of the blobs aside from the pointer in row?
|||"David Browne" wrote:

> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restored
> database to your real database.
> David
>
So far it looks like
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm will help me. My
frustrations seem to stem from a limitation with the other software I had
tried. I can see the correct data in the demo of log rescue... here's to
hoping.

Restoring deleted records with out of row blobs?

I have a database that had a number of rows in multiple tables deleted -
fortunately there was very little activity after these deletions (mostly more
deletions a few days later).
I have the log files, but the original inserts just have the pointer to the
blob. I tried generating undo scripts with apexsql Log but it doesn't even
reference the blob column when the script generates (restoring from a
detached mdf does give the pointer, but that pointer doesn't go anywhere and
a select on it is blank/null).
How is one supposed to go about restoring records / rolling back a
transaction log for deleting a row with an out-of-row blob? Am I totally
screwed even though nothing else happened in the database aside from the
deletes before the backup was taken?"Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>I have a database that had a number of rows in multiple tables deleted -
> fortunately there was very little activity after these deletions (mostly
> more
> deletions a few days later).
> I have the log files, but the original inserts just have the pointer to
> the
> blob. I tried generating undo scripts with apexsql Log but it doesn't
> even
> reference the blob column when the script generates (restoring from a
> detached mdf does give the pointer, but that pointer doesn't go anywhere
> and
> a select on it is blank/null).
> How is one supposed to go about restoring records / rolling back a
> transaction log for deleting a row with an out-of-row blob? Am I totally
> screwed even though nothing else happened in the database aside from the
> deletes before the backup was taken?
Use the last full backup and the subsequent log backups to do a
point-in-time restore to a new database. Then do INSERTS from the restored
database to your real database.
David|||"David Browne" wrote:
> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
> >I have a database that had a number of rows in multiple tables deleted -
> > fortunately there was very little activity after these deletions (mostly
> > more
> > deletions a few days later).
> >
> > I have the log files, but the original inserts just have the pointer to
> > the
> > blob. I tried generating undo scripts with apexsql Log but it doesn't
> > even
> > reference the blob column when the script generates (restoring from a
> > detached mdf does give the pointer, but that pointer doesn't go anywhere
> > and
> > a select on it is blank/null).
> >
> > How is one supposed to go about restoring records / rolling back a
> > transaction log for deleting a row with an out-of-row blob? Am I totally
> > screwed even though nothing else happened in the database aside from the
> > deletes before the backup was taken?
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restored
> database to your real database.
> David
>
So far it looks like
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm will help me. My
frustrations seem to stem from a limitation with the other software I had
tried. I can see the correct data in the demo of log rescue... here's to
hoping.

Restoring deleted records with out of row blobs?

I have a database that had a number of rows in multiple tables deleted -
fortunately there was very little activity after these deletions (mostly mor
e
deletions a few days later).
I have the log files, but the original inserts just have the pointer to the
blob. I tried generating undo scripts with apexsql Log but it doesn't even
reference the blob column when the script generates (restoring from a
detached mdf does give the pointer, but that pointer doesn't go anywhere and
a select on it is blank/null).
How is one supposed to go about restoring records / rolling back a
transaction log for deleting a row with an out-of-row blob? Am I totally
screwed even though nothing else happened in the database aside from the
deletes before the backup was taken?"Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in message
news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>I have a database that had a number of rows in multiple tables deleted -
> fortunately there was very little activity after these deletions (mostly
> more
> deletions a few days later).
> I have the log files, but the original inserts just have the pointer to
> the
> blob. I tried generating undo scripts with apexsql Log but it doesn't
> even
> reference the blob column when the script generates (restoring from a
> detached mdf does give the pointer, but that pointer doesn't go anywhere
> and
> a select on it is blank/null).
> How is one supposed to go about restoring records / rolling back a
> transaction log for deleting a row with an out-of-row blob? Am I totally
> screwed even though nothing else happened in the database aside from the
> deletes before the backup was taken?
Use the last full backup and the subsequent log backups to do a
point-in-time restore to a new database. Then do INSERTS from the restored
database to your real database.
David|||"David Browne" wrote:

> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in messag
e
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restore
d
> database to your real database.
> David
>
Unfortunately the database was created between full backups and the only
remaining full backup is after the fact. There's no way to roll back the
transactions from the ldf?
How are blobs handled in the transaction log? Is it just a limitation of
the log viewer that I'm using that I don't see the the anything about the
content of the blobs aside from the pointer in row?|||"David Browne" wrote:

> "Andrew Meinert" <AndrewMeinert@.discussions.microsoft.com> wrote in messag
e
> news:5CE119C0-80AE-4A16-A452-799A41971BC5@.microsoft.com...
>
> Use the last full backup and the subsequent log backups to do a
> point-in-time restore to a new database. Then do INSERTS from the restore
d
> database to your real database.
> David
>
So far it looks like
http://www.red-gate.com/products/SQ...escue/index.htm will help me. My
frustrations seem to stem from a limitation with the other software I had
tried. I can see the correct data in the demo of log rescue... here's to
hoping.