[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

export all data from one table to multiple text files

Posted on 2016-10-28
4
Medium Priority
?
108 Views
Last Modified: 2016-10-31
I have one table and want to create multiple text files with 200 rows of data in each file. I used row number to get a unique id but not able to generate multiple text files.

ROW_NUMBER() OVER(ORDER BY id DESC)

How to use the row_number in my between clause?
0
Comment
Question by:neehar gollapudi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 14

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 1000 total points
ID: 41864583
You can't use a Windowing function in the WHERE clause because row numbers are given to rows as part of the SELECT, whereas the WHERE clause is evaluated in the initial stages of the query execution.

What you can do is implement a paging solution in a stored procedure/script (https://nakulvachhrajani.com/2016/02/08/0373-sql-server-interview-questions-parameters-required-for-server-side-paging-mechanisms/) and then export the output of each page into individual text files.
0
 
LVL 32

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41864737
Try..Below code will give you number of records you want to fetch from each page.

--You can use below code

DECLARE @NoOfRecords AS INT = 200
DECLARE @WhichPage AS INT = 1


;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER(ORDER BY id DESC) rnk
	FROM [yourtableName]
)
SELECT [cols...] FROM CTE
WHERE Rnk >= (( @WhichPage * @NoOfRecords ) + 1 ) AND RNK <= ( ( @WhichPage + 1 ) * @NoOfRecords ) 


--

Open in new window


Hope it helps !!
0
 
LVL 2

Author Closing Comment

by:neehar gollapudi
ID: 41866853
Thank you Pawan and Nakul
0
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41866888
@Neehar,

Thanks for the points. Did you look at my solution? I proposed the same thing as Pawan with additional information (in my humble opinion) on why your code did not work and what can be used in case you migrate to SQL 2012.

If you are using SQL Server 2008 R2 or lower (which is not specified in the question), then the CTE method makes sense. If you are using SQL Server 2012 or higher, the OFFSET...FETCH is much better in terms of performance and helps overcomes some of the functional drawbacks of the conventional method.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question