Solved

export all data from one table to multiple text files

Posted on 2016-10-28
4
81 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 13

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 250 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 28

Accepted Solution

by:
Pawan Kumar earned 250 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 13

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 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