select statement - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select statement

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-08-23 : 10:28:38

I have a millions records in a table, I am going to run select script once at a time to return top 10 thausands at a time, i wanted select top 10,000 first execution and another 10,000 2nd execution so on...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-23 : 10:32:32

;with cte (select *, row_number() over (order by ....) AS recid
)
select *
from cte
where recid > 10000 * (@Execution - 1) and recid <= 10000 * @Execution

N 56°04'39.26"
E 12°55'05.63"

Sivangari
Starting Member

16 Posts

Posted - 2010-08-23 : 10:33:57

Can u explain clearly.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-08-23 : 10:43:15

@Peso, i am getting an error on @Execution.

@Sivangari: for example, i have 1000 data on the table.
I can list first 100 data like:
select top 100 * from table_Name

now, i wanted to display 101 to 200 data ...next is 201 to 300....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-23 : 10:45:00

DECLARE @Execution INT
SET @Execution = 1 -- First round

;with cte (select *, row_number() over (order by ....) AS recid
)
select *
from cte
where recid > 10000 * (@Execution - 1) and recid <= 10000 * @Execution

SET @Execution = 2 -- Second round

;with cte (select *, row_number() over (order by ....) AS recid
)
select *
from cte
where recid > 10000 * (@Execution - 1) and recid <= 10000 * @Execution

N 56°04'39.26"
E 12°55'05.63"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-08-23 : 12:35:28

@Peso, i got below error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 13:02:09

[code]DECLARE @Execution INT
SET @Execution = 1 -- First round

;with cte as (select *, row_number() over (order by ....) AS recid
)
select *
from cte
where recid > 10000 * (@Execution - 1) and recid <= 10000 * @Execution

SET @Execution = 2 -- Second round

;with cte as (select *, row_number() over (order by ....) AS recid
)
select *
from cte
where recid > 10000 * (@Execution - 1) and recid <= 10000 * @Execution
[/code]

also put correct column in place of ... after order by

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-08-23 : 17:05:20

still getting err, i tried belw script.

Declare @tbl1 table
(ID INT,
Col1 varchar(50),
Col2 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'Aa'

INSERT INTO @tbl1
SELECT 2,'bb'

INSERT INTO @tbl1
SELECT 3,'cc'

INSERT INTO @tbl1
SELECT 4,'dd'

INSERT INTO @tbl1
SELECT 5,'ee'

INSERT INTO @tbl1
SELECT 6,'ff'

INSERT INTO @tbl1
SELECT 7,'gg'

INSERT INTO @tbl1
SELECT 8,'hh'

INSERT INTO @tbl1
SELECT 9,'ii'

INSERT INTO @tbl1
SELECT 10,'jj'

INSERT INTO @tbl1
SELECT 11,'kk'

go

DECLARE @Execution INT
SET @Execution = 1 -- First round

;with @tbl1 as (select *, row_number() over (order by ID) AS recid
)
select *
from @tbl1
where recid > 5 * (@Execution - 1) and recid <= 5 * @Execution

SET @Execution = 2 -- Second round

;with @tbl1 as (select *, row_number() over (order by id) AS recid
)
select *
from @tbl1
where recid > 5 * (@Execution - 1) and recid <= 5 * @Execution

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-23 : 17:10:24

Works for me...

Declare @tbl1 table 
(ID INT,
Col1 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'Aa'

INSERT INTO @tbl1
SELECT 2,'bb'

INSERT INTO @tbl1
SELECT 3,'cc'

INSERT INTO @tbl1
SELECT 4,'dd'

INSERT INTO @tbl1
SELECT 5,'ee'

INSERT INTO @tbl1
SELECT 6,'ff'

INSERT INTO @tbl1
SELECT 7,'gg'

INSERT INTO @tbl1
SELECT 8,'hh'

INSERT INTO @tbl1
SELECT 9,'ii'

INSERT INTO @tbl1
SELECT 10,'jj'

INSERT INTO @tbl1
SELECT 11,'kk'

DECLARE @Execution INT
SET @Execution = 1 -- First round

;with cte as (select *, row_number() over (order by ID) AS recid from @tbl1
) 
select *
from cte
where recid > 5 * (@Execution - 1) and recid <= 5 * @Execution

SET @Execution = 2 -- Second round

;with cte as (select *, row_number() over (order by id) AS recid from @tbl1
) 
select *
from cte
where recid > 5 * (@Execution - 1) and recid <= 5 * @Execution

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 03:48:04

quote:
Originally posted by rudba

still getting err, i tried belw script.

Declare @tbl1 table
(ID INT,
Col1 varchar(50),
Col2 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'Aa'

INSERT INTO @tbl1
SELECT 2,'bb'

INSERT INTO @tbl1
SELECT 3,'cc'

INSERT INTO @tbl1
SELECT 4,'dd'

INSERT INTO @tbl1
SELECT 5,'ee'

INSERT INTO @tbl1
SELECT 6,'ff'

INSERT INTO @tbl1
SELECT 7,'gg'

INSERT INTO @tbl1
SELECT 8,'hh'

INSERT INTO @tbl1
SELECT 9,'ii'

INSERT INTO @tbl1
SELECT 10,'jj'

INSERT INTO @tbl1
SELECT 11,'kk'

go

DECLARE @Execution INT
SET @Execution = 1 -- First round

;with @tbl1 as (select *, row_number() over (order by ID) AS recid
)
select *
from @tbl1
where recid > 5 * (@Execution - 1) and recid <= 5 * @Execution

SET @Execution = 2 -- Second round

;with @tbl1 as (select *, row_number() over (order by id) AS recid
)
select *
from @tbl1
where recid > 5 * (@Execution - 1) and recid <= 5 * @Execution

Your insert statement has a problem. You need to list out the columns

INSERT INTO @tbl1(ID,col1)
SELECT 1,'Aa'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:44:59

you also have an additional column Col2. whats the purpose of that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-24 : 08:28:10

AND.

Even if you change all that -- the GO statement means that @tbl1 won't exist in the part with the common table expression. Tou can't use GO with table variables.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources