从SQL获取数据并将其复制到文件（.csv）中 [英] getting data from SQL and copy it in a file(.csv)

问题描述

我有一个生成报告的应用程序。应用程序调用存储过程，其中来自不同表的数据被采用并格式化并转储到临时文件中。该数据由应用程序获取，该数据基本上被转储到.csv文件中。由于从临时表中获取的数据很大，我们无法复制CSV中的整个数据。



我想的是，如果我分开整个数据分为5个部分，即每个20％，然后尝试填充csv，我想我可以得到所需的结果。但问题是如何将这些数据分成几部分？我应该在我的c＃代码中使用一个循环，调用它5次，在这种情况下，每次调用存储过程时，将在SP中生成从头开始的数据并获取和重叠记录。



任何人都可以建议我如何处理它？有没有人有不同的方法？



我最早需要一个解决方案，报告将在明天中午之后生成。

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.

推荐答案

有一篇关于此的好文章： http://blog.sqlauthority.com/2013/03/05/sql-server-exporting-query-results-to-csv-using-sqlcmd/



CodeProject的答案已经存在于更深入的细节中：如何使用Sql server存储过程将数据导出到.csv文件。

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.

所以问题不是将数据写入CSV而是将数据分区而不重叠，以便它可以写入多个文件？



如果是这样。



初始选择_all_数据到缓冲表



确保缓冲区表中的每一行都有一个主键（PK）。如果您提取的数据没有PK，那么使用标识列为缓冲表提供一个。



然后......



从主缓冲区表中选择前n行到辅助表（与主缓冲区相同的结构），其中n是为您提供合适的文件大小。



如果辅助表不为空...

- 将辅助表的内容写入文件。

- 从主缓冲表内删除加入辅助表（这就是为什么PK是一个好主意）扔掉刚刚导出的数据。

- 清空辅助表并重复下一行。



在C＃和SQL之间分配负载的方式取决于你。

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.

这篇关于从SQL获取数据并将其复制到文件（.csv）中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！