Edited at

BCPを使ってテーブルをCSVに吐き出すストアドプロシージャー

More than 3 years have passed since last update.

BCPコマンドを利用してパ指定したテーブルをCSVファイルに吐き出すストアドプロシージャーの作り方です。

ただしBCPコマンドではヘッダー行が出力されないので、SQLに工夫をしています。

BCPコマンドはコマンドプロンプトから実行できるコマンドで、これを利用するには先にxp_cmdshellを使えるようにしておきます。


xp_cmdshellサーバー構成オプション

-- To allow advanced options to be changed.  

EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

以下のストアドを作ります。


ExportCSV

USE [HOGEHOGE]

GO
/****** Object: StoredProcedure [dbo].[ExportCSV] Script Date: 2016/08/25 17:18:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ExportCSV]
-- Add the parameters for the stored procedure here
@TableName NVARCHAR(100),
@FilePath NVARCHAR(255)
AS
BEGIN

SET NOCOUNT ON;
DECLARE @raw_sql nvarchar(4000)

DECLARE @columnHeader VARCHAR(8000)
SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

DECLARE @ColumnList VARCHAR(8000)
SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ 'CAST(['+column_name +'] AS VARCHAR)' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@TableName

-- PRINT @ColumnList

SELECT @raw_sql = 'SELECT '+ @columnHeader +' UNION ALL SELECT ' + @ColumnList + ' FROM [' + @TableName +']'

-- PRINT @raw_SQL
-- EXECUTE sp_executesql @raw_sql

DECLARE @sql VARCHAR(8000);
SELECT @sql = 'bcp "'+@raw_sql+ '" queryout '+@FilePath +' -c -t, -T -S' + @@servername + ' -d '+DB_NAME()

EXEC master..xp_cmdshell @sql

END


ストアドを呼び出すとCSVファイルが生成されます。


ストアドを呼び出す。

EXEC dbo.ExportCSV 'テーブル名','c:\tmp\test.csv'