May 29, 2012 at 6:39 am
Hi,
we have a code in oracle which we need to convert to sql server query. Here we are trying to retrieve the data batch by batch... say first 2000 in one batch and then the second 2000 in the next batch. the oracle code is ,
SELECT * FROM (
SELECT table_name.*, row_number() OVER (ORDER BY ROWID ASC) rn FROM table_name) WHERE rn BETWEEN 2001 AND 4000 ORDER BY rn;
Here the table_name comes dynamically.
I tried like this
SELECT * FROM (
SELECT table_name.*, row_number() OVER (ORDER BY (select 1) ASC) AS rn FROM table_name) as A WHERE A.rn BETWEEN 1 AND 100 ORDER BY A.rn;
and like this also
SELECT * FROM (
SELECT table_name.*, row_number() OVER (ORDER BY (SELECT
top 1 c.name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name = 'table_name') ASC) rn FROM table_name) as A WHERE A.rn BETWEEN 2001 AND 4000 ORDER BY A.rn;
But the problem is that, it is not guaranted that the rows won't repeat themselves in between the batches.
Any help will be appreciated.
May 29, 2012 at 7:08 am
you will need to use some unique key, try using primary keys in:
row_number() OVER (ORDER BY [YourPKColumnOrColumns])
May 29, 2012 at 10:23 am
I think, that won't work... As I already said that the table_ name comes from a user input. I am do not know which table it is going to ask for from some hundred tables. I am sure it sounds like unbelievable, but there are few tables which does not have any primary key. How to approach this?
May 29, 2012 at 10:50 am
If the tables have identity columns, you can use $IDENTITY as the column name.
If not, you might have to dynamically generate the code based on unique indexes on the table at the time the statement is to be run.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply