x

query for count

good day, After running query (select * from ROSS )I received 20 lacs rows,now those can not be exported to xl/csv....since xl has 10 lacs rows limitation.and even if I try to export it to xl/csv only 10 lacs rows getting saved

Is there any command which give me records between 10lac and 20 lac row?to export remaining records?

rgds,

more ▼

asked Jan 28, 2011 at 05:50 AM in Default

avatar image

pits
830 89 93 95

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

If you've not got a handy ID field, you could use a partitioning function. Something like:

 WITH RowNumberedCTE AS (
     SELECT Field1, Field2, Field3, ROW_NUMBER() OVER (ORDER BY Field1) AS RowNumber
     FROM TableName
 )
 SELECT Field1, Field2, Field3 
 FROM RowNumberedCTE
 WHERE RowNumber between 1 AND 10000 -- change this to get the next trenche of data
 ORDER BY RowNumber
more ▼

answered Jan 31, 2011 at 10:15 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

(comments are locked)
10|1200 characters needed characters left

Try

 Select * from ROSS Where Row_ID <= 100000 

 Select * from ROSS Where Row_ID Between 100001 and 200000

 etc...

Or

 --You need to do the following way by creating the 
 --Table Variable if you haven't got RowID

 Declare @temp as Table (RowID int Identity(1,1),
                         Col1 varchar(...), Col2 varchar(...), etc...)

 Insert into @temp
 Select Col1 from ROSS

 Select Col1 From @temp Where RowID <= 100000    --you need to assign value here 
                                                 --/ limit of XL rows


 


more ▼

answered Jan 28, 2011 at 06:16 AM

avatar image

Leo
1.6k 55 59 62

(comments are locked)
10|1200 characters needed characters left

Dont use select * in SSMS to export data. You would be much better off using SSIS to manage the extract and creation of the csv file. If you want a wizard to guide you through the steps then right click on your database and choose Tasks... Export Data. It will take you through the steps to export the data you want into a csv of your choice.

more ▼

answered Jan 28, 2011 at 06:18 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

If you have excel 2010, install the power pivot extension from here Export your data to a text file then import to power pivot, it can handle millions of rows with highly efficient columnar compression.

more ▼

answered Jan 31, 2011 at 06:04 PM

avatar image

Scot Hauder
6.5k 13 16 22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x461
x53
x4

asked: Jan 28, 2011 at 05:50 AM

Seen: 1136 times

Last Updated: Jan 28, 2011 at 05:50 AM

Copyright 2018 Redgate Software. Privacy Policy