How to construct the BCP or the Microsoft SQL Server to ingore the right truncation error?

[SevenSeaGrass]

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?

Thanks.

Charlie

 

[SQLBill]

Not that I'm aware of, but one solution is to create a "staging" table with the column set to what the true length is, then use TSQL to copy the data from the staging table to the actual table while converting the stock price to the length you really want.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[philhege]

A lot of high-freak traders make big moolah from that half a cent. Are you sure you want to drop it?

;-b...

-----------
With business clients like mine, you'd be better off herding cats.