CODEWITHSUNDEEP
decimalssmsbcpapplication-settingsmaxlength
Ron
Ron

Reputation: 21

BCP QUERYOUT how to change length to all decimal fields

I export SQL tables to txt files (by the code below in ssms)
all the columns are exported well except decimal columns, which export 41 chars (I want 19 chars),
even the size column is 14(4)
how can I change the settings in order the column will export in the size I want?

notes:
bcp export 0.0 decimal value as .0000 as I need
my tables is very big can't use substring- a lot of columns and a lot of rows

EXEC sp_configure 'show advanced options', 1 

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1 

RECONFIGURE
-------------
DECLARE @stmtQuery VARCHAR(8000),@stmt VARCHAR(8000);
       
----שליפת הנתונים מהטבלה
set @stmtQuery ='SELECT  * FROM myDB.dbo.HugeTable' --a lot of decimal columns, a lot of rows

--copy data to txt file
SET @stmt = 'BCP "' + @stmtQuery +
'" QUERYOUT "path\to\file\TableData.txt"  -t -C RAW   -S ' + @@SERVERNAME + ' -d ' +  DB_NAME()+' -e path\to\file\log.txt -c  -r"0x0A" -T';
EXEC master.sys.xp_cmdshell @stmt;

Upvotes: 0

Views: 420

Answers (1)

Frenk
Frenk

Reputation: 17

you need to cast your decimal columns to varchar before export with bcp

set @stmtQuery ='SELECT convert(varchar,MyDecimalColumn) as ColumnName FROM myDB.dbo.HugeTable'

alternatively you can use the FORMAT() function if you're using MSSQL 2012+

set @stmtQuery ='SELECT FORMAT(MyDecimalColumn,''00000.0000000000'') as ColumnName FROM myDB.dbo.HugeTable'

i'll hope it helps

Upvotes: 1

Related Questions