getting data from SQL and copy it in a file(.csv)

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

I have an application which generates a report . The application calls a Stored Procedure where the data from different tables is taken and formatted and dumped in a temporary file. This data is taken by the application , which is bascially dumped in the .csv file. Since the data taken from the temporary table is huge, we are not able to copy the entire data in the CSv.

What I am thinking is that if I divide the entire data in 5 parts i.e 20 % each and then try to populate the csv , i guess i can get the desired result. But the problem is how can take this data in parts ? should i use a loop in my c# code, calling it 5 times,in that case each time I call the stored procedure the data from the beginning will be generated in the SP and will get and overlapping records.

Can any one please suggest how can i proceed with it ? Does any one has a different approach ?

I need a solution at the earliest, the report is to be generated tomorrow after noon.

Posted 17-Mar-14 4:34am

Member 10676643

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

NvsYazilim · Answer 1 · 2014-03-17T05:06:00

Solution 1

There is a good article about this at: http://blog.sqlauthority.com/2013/03/05/sql-server-exporting-query-results-to-csv-using-sqlcmd/

And a CodeProject answer already exist in much deeper detail at: How To Export Data To the .csv file using Sql server Stored Procedure.

Posted 17-Mar-14 5:06am

NvsYazilim

cigwork · Answer 2 · 2014-03-17T10:32:00

So the problem is not the writing the data to CSV but partitioning the data without overlap so that it can be written to multiple files?

If so.

Initial select of _all_ data into a buffer table

Make sure that each row in your buffer table has a primary key (PK). If your extracted data doesn't have a PK then provide the buffer table with one using an identity column.

Then ...

Select top n rows from your main buffer table into a secondary table (same structure as main buffer) where n is whatever will give you a suitable filesize.

If the secondary table isn't empty...
- Write the content of your secondary table to file.
- Delete from main buffer table inner join on secondary table (this is why the PK is a good idea) to throw away the data just exported.
- Empty the secondary table and repeat for next n rows.

The way you split the load between C# & SQL is up to you.