Stored Procedure to Export a Table via BCP

If you are looking for a generic way to export table data via BCP, here’s how:

 
CREATE PROCEDURE [dbo].[u_ExportDataViaBcp]
(
@name VARCHAR(100), -- this is the table (or table function) name to export
@file VARCHAR(200), -- output file
@fmt_date INT = 121,
@fmt_decimal INT = 128,
@fmt_money INT = 1,
@fmt_float INT = NULL,
@quoted BIT = 1 -- whether or not varchar fields should be quoted on output
)
AS
 
SET NOCOUNT ON
 
--- get the current server instance
DECLARE @server NVARCHAR(128)
SET @server = CONVERT(NVARCHAR(128), SERVERPROPERTY('servername'));
 
-- get the current db
DECLARE @db NVARCHAR(100)
SET @db = DB_NAME()
 
-- make sure these tmp tables will be unique
DECLARE @tmpUniqueId VARCHAR(50)
SELECT @tmpUniqueId = REPLACE(CONVERT(VARCHAR(50),NEWID()),'-','')
 
-- sql that gets executed
DECLARE	@tmpSql VARCHAR(8000)
 
-- the temp tables used
DECLARE @export_temp VARCHAR(100)
SELECT @export_temp = '##BcpExportTmp_' + @tmpUniqueId
DECLARE @export_temp2 VARCHAR(100)
SELECT @export_temp2 = '##BcpExportTmp2_' + @tmpUniqueId
 
-- Make sure the (global) temp tables have been cleaned up
IF OBJECT_ID('tempdb..' + @export_temp) IS NOT NULL
BEGIN
SELECT @tmpSql = 'DROP TABLE ' + @export_temp
EXEC(@tmpSql)
END
IF OBJECT_ID('tempdb..' + @export_temp2) IS NOT NULL
BEGIN
SELECT @tmpSql = 'DROP TABLE ' + @export_temp2
EXEC(@tmpSql)
END
 
-- in case we have an error
DECLARE @error VARCHAR(200)
 
-- USE the database
SELECT @tmpSql = 'USE ' + @db + ';'
PRINT @tmpSql
EXEC(@tmpSql)
 
-- Execute the use and the select
SELECT @tmpSql = 'SELECT * INTO ' + @export_temp + ' FROM ' + @name  + ';'
PRINT @tmpSql
EXEC(@tmpSql)
 
-- Build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar from their original type
DECLARE
@columnNames VARCHAR(MAX),
@columnConversions VARCHAR(MAX)
SELECT
@columnNames =
COALESCE(@columnNames + ',', '') +
'[' + column_name + ']',
@columnConversions =
COALESCE(@columnConversions  + ',', '') +
CASE
WHEN data_type IN ('VARCHAR','NVARCHAR') AND @quoted = 1 THEN '''"'' + [' + column_name + '] + ''"''' -- throw some quotes around it
ELSE -- do some conversion
'CONVERT(VARCHAR(255),[' + column_name + ']' +
CASE
WHEN data_type IN ('NUMERIC', 'DECIMAL') THEN CASE WHEN @fmt_decimal IS NOT NULL THEN ',' + CONVERT(VARCHAR, @fmt_decimal) ELSE '' END
WHEN data_type IN ('MONEY', 'SMALLMONEY') THEN CASE WHEN @fmt_money IS NOT NULL THEN ',' + CONVERT(VARCHAR, @fmt_money) ELSE '' END
WHEN data_type IN ('DATETIME', 'SMALLDATETIME') THEN CASE WHEN @fmt_date IS NOT NULL THEN ',' +  + CONVERT(VARCHAR, @fmt_date) ELSE '' END
WHEN data_type IN ('FLOAT', 'REAL') THEN CASE WHEN @fmt_float IS NOT NULL THEN ',' +  + CONVERT(VARCHAR, @fmt_float) ELSE '' END
ELSE ''
END + ')'
END +
' AS [' + column_name + ']'
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @export_temp
 
-- execute select query to insert data and column names into new temp table
SELECT @tmpSql =
'SELECT ' + @columnNames + ' ' +
'INTO ' + @export_temp2 + ' ' +
'FROM ( ' +
' SELECT ' + @columnConversions + ', 2 AS tmpsort FROM ' + @export_temp + ' ' +
' UNION ALL ' +
' SELECT ''' + REPLACE(REPLACE(REPLACE(@columnNames, ',', ''', '''),'[',''),']','') + ''', 1 AS tmpsort ' +
' ) AS x ' +
' ORDER BY x.tmpsort; ';
 
PRINT @tmpSql
EXEC(@tmpSql)
 
-- Execute the BCP to create the file
SELECT @tmpSql = 'bcp "SELECT * FROM ' + @export_temp2 + '" queryout "' + @file + '" -c -C RAW -t, -T -S' + @server
EXEC master.sys.xp_cmdshell @tmpSql
IF @@ERROR > 0
BEGIN
SELECT @error = 'Couldn''t execute the bcp: ''' + @tmpSql;
RAISERROR(@error, 16, 1)
END
 
-- Make sure the (global) temp tables have been cleaned up
IF OBJECT_ID('tempdb..' + @export_temp) IS NOT NULL
BEGIN
SELECT @tmpSql = 'DROP TABLE ' + @export_temp
EXEC(@tmpSql)
END
IF OBJECT_ID('tempdb..' + @export_temp2) IS NOT NULL
BEGIN
SELECT @tmpSql = 'DROP TABLE ' + @export_temp2
EXEC(@tmpSql)
END
 
GO

This does use global temp tables as well as xp_cmdshell, but it also gets the job done.

This is a modified version of the suggestion found here:
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx

Thanks to them for actually doing the hard work!  I just cleaned it up and made it support varied column names, quoted varchar/nvarchar output, and use the current server and database instance.

This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *