Solved

export all data from one table to multiple text files

Posted on 2016-10-28
4
67 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
  • 2
4 Comments
 
LVL 11

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 250 total points
Comment Utility
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 17

Accepted Solution

by:
Pawan Kumar Khowal earned 250 total points
Comment Utility
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 1

Author Closing Comment

by:neehar gollapudi
Comment Utility
Thank you Pawan and Nakul
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
Comment Utility
@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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now