divide data into two parts - 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 2000 Forums

SQL Server Development (2000)

divide data into two parts

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-05-25 : 04:10:00

Hi,

I have data in one table which has 90000 records.
my procedure displays 90000 records, but it is impossible for me to paste this in excel as excel row limitation is 65000 rows.

I want when my procedure runs the data is displayed into chunks
i.e 50000 records and 40000 records

Kindly help.

Rgards
Sachin

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 04:21:10

Get a later version of excel?
Better - don't try to paste this number of rows into excel.

select *, identity(int,1,1) as z_seq
into #a
from tbl

select * from #a where z_seq <= 50000
select * from #a where z_seq > 50000

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-05-25 : 04:52:39

Thanks a lot that helped

Best Regards!!

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-05-25 : 05:04:00

What if I have records in table 200000

the in first temp i will have 50000 which i can paste in excel
but the next will have 150000 records ..... again the same issue

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 05:08:08

quote:
but the next will have 150000 records ..... again the same issue

Do you understand the solution nr posted there at all ?


select * from #a where z_seq <= 50000
select * from #a where z_seq >  50000 and z_seq <= 100000
select * from #a where z_seq > 100000 and z_seq <= 150000
select * from #a where z_seq > 150000 and z_seq <= 200000

KH
[spoiler]Time is always against us[/spoiler]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 05:09:47

declare @id int
select @id = 1
while @id < select max(z_seq) from #a
begin
select * from #a where z_seq between @id and @id + 50000-1
select @id = @id + 500000
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-05-25 : 06:02:48

Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources