Transact SQL: exporting table or view in CSV

Submitted by st on

It's pretty easy on SQL Server but pay attention to the database/server rights of used connection. Usually, the preparing like sp_configure requires a system administrator privileges.

Activate "xp_cmdshell" option (only once)

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Create and run wrapping stored procedure

USE AdventureWorks
GO
CREATE PROCEDURE dbo.export_to_csv
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @file_name varchar(1000);
  SET @file_name = 'c:\temp\'; -- SQL Server account should have rights to write here
  SET @file_name = @file_name + 
    convert(varchar(16), convert(int, (rand() * 1000000000))) + '.csv';
  DECLARE @sql varchar(8000);
  SET @sql = 'bcp AdventureWorks.Person.Contact out "' + 
    @file_name + '" -c -t; -T -S' + @@servername;
  EXEC xp_cmdshell @sql, no_output;
END
GO