如何使用Sql server存储过程将数据导出到.csv文件。 [英] How To Export Data To the .csv file using Sql server Stored Procedure.

问题描述

大家好



我有一个Sql存储过程，我想从我的存储过程中将数据导出到.csv文件。我的程序是 -

Hi All

I have an Sql Stored Procedure and i want to export data to the .csv file from my Stored Procedure. my procedure is --

create Proc ExportTest
AS
BEGIN
SELECT * FROM EMPLOYEE

-- I NEED TO EXPORT RESULT OF THIS SELECT STATEMENT TO THE .CSV FILE
END

谢谢

Thanks

推荐答案

如果你可以使用命令行，我建议你查看 bcp实用程序 [ ^ ]。它是一个功能强大的命令行工具，允许以各种格式导出（或导入）数据。



这里 [ ^ ]你可以阅读一篇关于如何使用该实用程序的好文章几个例子。



如果您遇到麻烦，请在下面留言。我们将能够为您提供帮助。

If you can use a command line, I suggest you check the bcp utility[^]. It is a powerful command line tool that allows exporting (or importing) data in various formats.

Here[^] you can read a nice article on how to use the utility with a few examples.

If you have troubles with it, just comment below. We will be able to assist you.

您可以使用查询如下所示

you can use query Like given below

declare @sql varchar(8000)
select @sql = 'bcp "select * from EmailVarification..tblTransaction" queryout c:\bcp\Tom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

此查询中的
EmailVarification是数据库名称，tblTransaction是表名。

执行此查询时必须设置写文件的路径。

目前在此查询中您必须创建bcp文件夹在C：\驱动器中。



谢谢

in this query EmailVarification is DB name and tblTransaction is table name.
for execute this query you have to set path for write file.
currently in this query you have to create a "bcp" folder in "C:\ "Drive .

Thanks

是的，可以从存储过程中执行此操作。我们目前在我们的生产环境中有这样的工作。



使用T-SQL

Yes, it is possible to do it from a stored procedure. We currently have something like this working in our production environment.

Use T-SQL

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
SELECT Field1, Field2, Field3 FROM DatabaseName

但是，有几点需要注意：



1.您需要提供Microsoft.ACE.OLEDB.12.0提供程序。 Jet 4.0提供程序也可以工作，但它很古老，所以我使用了这个。



2. .CSV文件必须已经存在。如果您正在使用标题（HDR = YES），请确保.CSV文件的第一行是所有字段的分隔列表。

But, there's a couple of caveats:

1. You need to have the Microsoft.ACE.OLEDB.12.0 provider available. The Jet 4.0 provider will work, too, but it's ancient, so I used this one instead.

2. The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.

这篇关于如何使用Sql server存储过程将数据导出到.csv文件。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！