06 February 2007 08:27
tonyrogerson
Importing Visual Basic 6 Currency type using BULK INSERT / BCP
I've been spending a couple of weeks on a file migration into SQL Server where the client's data is stored in files written using VB's GET and PUT (fixed length records).
I was at a loss trying to import the Currency data type until I found this really useful piece of information that shows how Currency is actually stored -> http://articles.techrepublic.com.com/5100-3513_11-5885671.html (thanks to Peter Aitken).
Anyway, the crunch is that Currency is stored using 8 bytes as a BIGINT in our terms but scaled by 4 decimal places.
So, all you do is to import the data as BIGINT and then convert to DECIMAL( 28, 4 ) and divide the number by 10,000.
I know this isn't detail but my mate Trevor Dwyer as commented I haven't posted since the 11th, the M25 is taking my time at the moment so will be back to normal output in the next few weeks.
Tony.
Filed under: SQL Server