extracting the specified record no of records from database table

Hot Topic

Exporting XML using bcp
Index on bit fields in SQL Server Management Studio
help with a max length table constraint
RTM Integration Services Job
The report server cannot open a connection to the report server database
sending "Empty string" to a textbox on the report
Retrieving and storing file timestamp
Invalid length parameter passed to the SUBSTRING function
Volatile parent key
How to exclude read-only connections from DTC transactions?

SQL Server

Cannot create new Subscription?
SQL reporting services 2005 with CLR
Database Mail Fails For SQL Server Agent Jobs / Maintenace plans - Why ?
How to get a reference to output buffers in a ProcessInput()
Printing Blank in Landscape
messages aren't getting through after backup / restore on a different server...
Restoring a databse user
Removing duplicates
Using result sets from a stored procedure
How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order

extracting the specified record no of records from database table

hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

Answer this question

extracting the specified record no of records from database table

  • abhig

    If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:
    select *
    from (select *, row_number() over(order by CustomerId) as rownum
    from customers
    ) as c
    where c.rownum between 1000 and 10000;
    If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).
    Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:
    declare @n int
    set @n = 1000 -- set number of rows you want to insert at a time
    set rowcount @n
    while(1=1)
    begin
    insert into MasterCustomers
    select ...
    from Customers as c1
    where not exists(
    select * from MasterCustomers as c2
    where c2.CustomerName = c1.CustomerName)
    if @@rowcount = 0 break
    end
    set rowcount 0
    -- using TOP
    declare @n int
    set @n = 1000 -- set number of rows you want to insert at a time
    while(1=1)
    begin
    insert top(@n) into MasterCustomers
    select ...
    from Customers as c1
    where not exists(
    select * from MasterCustomers as c2
    where c2.CustomerName = c1.CustomerName)
    if @@rowcount = 0 break
    end


  • cdolor

    if you use sql2005

    you also can use the function ROW_NUMBER() to

    select rownum between 1000 and 10000 like oracle

    example:

    with customers _temp as

    (SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
    from customers)
    select *
    from customers _temp
    where RowNumber between 1000 and 10000



  • Kartik Subramani

    There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.

  • EasternBoy

    SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
    select * from
    (select top 1000 * from (
    select top 11000 *
    from customers
    order by customer_id asc
    ) as tmp1
    order by cutomer_id desc
    ) as tmp2
    order by ...

    for extract customers betweeen 10000 and 11000 based on customer_id

  • extracting the specified record no of records from database table
Answer this question