CODEWITHSUNDEEP
sqlsql-serversql-server-2005
SeaGrass
SeaGrass

Reputation: 31

ignore bcp right truncation

I have a file with the stock information, such as ticker and stock price. The file was loaded to database table using freebcp. The stock price format in the file is like: 23.125. The stock price data type in database table is [decimal](28, 2). freebcp loaded the data to the table without any problem by ignoring the last digit: 23.12 was loaded to the table column of the record. We are now using Microsoft SQL Server's bcp utility (Version: 11.0 ) to load the data. However we now encounter an issue: bcp considers loading 23.125 to decimal(28.2) is an error (#@ Row 783, Column 23: String data, right truncation @#). It rejected the record.

I didn't want to modify the input file, because there are a lot of columns in the file need to be fixed by removing the last digit of columns.

Are there any ways to construct the BCP or the Microsoft SQL Server to ingore the right truncation error?

Upvotes: 3

Views: 2530

Answers (2)

jose_bacoy
jose_bacoy

Reputation: 12704

I encountered this error today and I fixed this by using the -m parameter in sql server version #15

bcp dbo.<table> in <csv file> -S <server> -d <db> -U <user> -P <psw> -m 999999 -q -c -t ,

Reference: https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15#m

Note: The -m option also does not apply to converting the money or bigint data types.

Upvotes: 0

Greg
Greg

Reputation: 4055

A common workaround back in the day, is to BCP into a secondary/temp table, then do SELECT (columnlist) INTO the base table, with the necessary conversion. Another option, is to Use the OPENROWSET Bulk Rowset Provider, then you can cast/convert as needed.

Upvotes: 1

Related Questions