Solved

export all data from one table to multiple text files

Posted on 2016-10-28
4
73 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 12

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 12

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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