?
Solved

export all data from one table to multiple text files

Posted on 2016-10-28
4
Medium Priority
?
94 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 29

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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.
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

765 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