[MSSQL] Extract data menggunakan BCP

Ini salah satu cara untuk mengextract data dari database MSSQL kedalam bentuk CSV yang paling mudah (cara bodoh :D )



ALTER PROCEDURE dbo.p_x_DbExtract
@dTBASE_name VARCHAR(200) , -- Database Name
@TABLE_Name VARCHAR(200) , -- Table Name ( if empty then all tables in database will be extracted )
@path VARCHAR(50) , -- Path for CSV file
@server VARCHAR(100) , -- Server name ( default localhost)
@saPasswd VARCHAR(20) -- Password for SA

AS
BEGIN
/*
Author : Andy Utomo

DECLARE
@field_Name VARCHAR(200) ,
@schema_Name VARCHAR(100) ,
@fileName VARCHAR(200) ,
@textMsg VARCHAR(6000)

DECLARE @rowCount int , @rowMax int

DECLARE @table table (
idx int identity( 1,1) ,
dTBASE_name VARCHAR(200) ,
schema_Name VARCHAR(200) ,
field_Name VARCHAR(200)
)
IF ISNULL( @dTBASE_name ,'') = ''
BEGIN
SELECT @dTBASE_name = TABLE_CATALOG from INFORMATION_SCHEMA.TABLES
END
if isnull(@path ,'') = ''
begin
set @path = 'D:\data\'
end
if isnull(@server ,'') = ''
begin
set @server = 'localhost'
end
if isnull(@saPasswd ,'') = ''
begin
set @saPasswd = 'sa'
end

IF ISNULL( @TABLE_Name ,'' ) =''
BEGIN
INSERT INTO @table
SELECT table_catalog , table_schema , TABLE_NAME
FROM information_Schema.tables
WHERE table_catalog = @dTBASE_name
AND table_type = 'BASE TABLE'
AND TABLE_NAME <>'dtproperties'
END
ELSE
BEGIN
INSERT INTO @table
SELECT table_catalog , table_schema , TABLE_NAME
FROM information_Schema.tables
WHERE table_catalog = @dTBASE_name
AND table_type = 'BASE TABLE'
AND TABLE_NAME <>'dtproperties'
AND @TABLE_Name = TABLE_NAME
END


set @rowCount = 1
SELECT @rowMax = count(*) from @table
while @rowCount <= @rowMax
begin
SELECT
@dTBASE_name = dTBASE_name ,
@field_Name = field_Name,
@schema_Name = schema_Name
from @table where idx = @rowCount
set @textMsg = 'bcp '+ @dTBASE_name + '.'+ @schema_Name +'.' + @field_Name +' out ' + @path + @field_Name+ '.csv -c -t \\ -r \n -S ' + @server + ' -Usa -P'+ @saPasswd + ' '

exec master..xp_cmdshell @textMsg ,NO_OUTPUT
set @rowCount = @rowCount + 1
end
END