Uncategorized

BCP Issues and Solutions

BCP Issues – A way to sort it out.

Reference:

http://sqlfool.com/2012/04/bcp-script-generator/

-- User-defined variables --
 
DECLARE @tableToBCP NVARCHAR(128) = 'AdventureWorksDW2008R2.dbo.DimCustomer'
, @Top VARCHAR(10) = NULL -- Leave NULL for all rows
, @Delimiter VARCHAR(4) = '|'
, @UseNULL BIT = 1
, @OverrideChar CHAR(1) = '~'
, @MaxDop CHAR(1) = '1'
, @Directory VARCHAR(256) = 'C:bcp_output';
 
 
-- Script-defined variables --
 
DECLARE @columnList TABLE (columnID INT);
 
DECLARE @bcpStatement NVARCHAR(MAX) = 'BCP "SELECT '
, @currentID INT
, @firstID INT;
 
INSERT INTO @columnList
SELECT column_id
FROM sys.columns
WHERE object_id = OBJECT_ID(@tableToBCP)
ORDER BY column_id;
 
IF @Top IS NOT NULL
SET @bcpStatement = @bcpStatement + 'TOP (' + @Top + ') ';
 
SELECT @firstID = MIN(columnID) FROM @columnList;
 
WHILE EXISTS(SELECT * FROM @columnList)
BEGIN
 
SELECT @currentID = MIN(columnID) FROM @columnList;
 
IF @currentID <> @firstID
SET @bcpStatement = @bcpStatement + ',';
 
SELECT @bcpStatement = @bcpStatement +
CASE
WHEN user_type_id IN (231, 167, 175, 239)
THEN 'CASE WHEN ' + name + ' = '''' THEN '
+ CASE
WHEN is_nullable = 1 THEN 'NULL'
ELSE '''' + REPLICATE(@OverrideChar, max_length) + ''''
END
+ ' WHEN ' + name + ' LIKE ''%' + @Delimiter + '%'''
+ ' OR ' + name + ' LIKE ''%'' + CHAR(9) + ''%''' -- tab
+ ' OR ' + name + ' LIKE ''%'' + CHAR(10) + ''%''' -- line feed
+ ' OR ' + name + ' LIKE ''%'' + CHAR(13) + ''%''' -- carriage return
+ ' THEN '
+ CASE
WHEN is_nullable = 1 THEN 'NULL'
ELSE '''' + REPLICATE(@OverrideChar, max_length) + ''''
END
+ ' ELSE ' + name + ' END'
ELSE name
END
FROM sys.columns
WHERE object_id = OBJECT_ID(@tableToBCP)
AND column_id = @currentID;
 
DELETE FROM @columnList WHERE columnID = @currentID;
 
 
END;
 
SET @bcpStatement = @bcpStatement + ' FROM ' + @tableToBCP
+ ' WITH (NOLOCK) OPTION (MAXDOP 1);" queryOut '
+ @Directory + REPLACE(@tableToBCP, '.', '_') + '.dat -S' + @@SERVERNAME
+ ' -T -t"' + @Delimiter + '" -c -C;'
 
SELECT @bcpStatement;

Leave a Reply

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