Stored Procedure to Write CSV - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Stored Procedure to Write CSV

Author

Topic

willy
Starting Member

1 Post

Posted - 2002-10-30 : 20:22:28

Hi everyone,

I'm quite new to all of this and I'm not sure how to use the write method in a sql server stored procedure to write to a csv file from selecting from the XExportTable. I know that I have to use a cursor and I've started with the following:

CREATE PROCEDURE XWriteCSV

as

DECLARE csv_csr CURSOR FOR
SELECT * FROM XExportTable
OPEN csv_csr
FETCH NEXT FROM csv_csr
WHILE @@FETCH_STATUS = 0

BEGIN

declare @fno varchar(30)
declare
begin tran

--
--
--
--
--

FETCH NEXT FROM csv_csr
--commit
END
CLOSE csv_csr

Thank you for any suggestions!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-30 : 22:07:08

You don't need a cursor to do any of that. You can output data from a SQL Server table directly to a CSV file using bcp or DTS (Data Transformation Services). Both of these are fully documented in SQL Server Books Online. As an example of how easy it is to use bcp:

bcp "SELECT * FROM pubs..authors" queryout C:\authors.txt -Sserver -Uuser -Ppassword -c -t,

You can run that directly from the server's command line, or run it from a SQL Server stored procedure or batch with the following:

EXECUTE master..xp_cmdshell 'bcp "SELECT * FROM pubs..authors" queryout C:\authors.txt -Sserver -Uuser -Ppassword -c -t,'

You simply need to change the server name, user and password to use legitimate values for your SQL Server, and change the query used for the data you want to ouput and the name of the output file. Books Online has more examples of how bcp works, and describes DTS in great detail as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources