How to Bulk Insert string data into a money colum?

How to Bulk Insert string data into a money colum?

Format files work great to import string data into a char column.

But I cannot convert a char column to either a money or numeric data type.

I get datatype errors when bulk inserting string data into a money column.

The string data is 22 characters long, no decimal point, trailing negative sign.

Here is an example: 000000000000007898384-

My goal: Bulk Insert the above sample string into a money column.

Often, for situations like this, it is useful to first import into a 'staging' table (same datatypes as import data),

AND then execute 'clean-up' routines

BEFORE moving the data into the production tables.

This allows correcting alot of data anomolies that cannot be handled with a format file.

|||

Thanks, Arnie, for the quick response. What clean-up routines did you have in mind?

Using Enterprise Manager to change the destination column's data type from "char" to "money" or "int" won't work.

Are you talking about concatenating a decimal point to the end of the source string, in hopes that the decimal point will help me get past the conversion issue?

|||

John,

Something like this could work for your situation:

Code Snippet

DECLARE
@.MyString varchar(25),
@.MyMoney money

SET @.MyString = '000000000000007898384-'

IF right( @.MyString, 1 ) = '-'
SET @.MyMoney = cast( ( '-' + left( @.MyString, ( len( @.MyString ) - 1 ))) AS money )
ELSE
SET @.MyMoney = cast( @.MyString AS money )

SELECT @.MyMoney

-7898384.0000

|||

So, it looks like you're putting the negative sign at the front as needed and leaving the positive numbers alone, before casting the result as money. This is fine.

My SQL table has 23 columns and millions of rows.

Column 10 is char, 22 characters wide. Negative signs are trailing.

How would I apply the code to all the rows of column 10 in that table?

|||

You would build a CASE structure (something like this):

DECLARE @.MyTable table
( RowID int IDENTITY,
Column10 varchar(50)
)

INSERT INTO @.MyTable VALUES ( '000000000000007898384-' )
INSERT INTO @.MyTable VALUES ( '000000000000007898385' )

SELECT
RowID,
MyMoney = cast( stuff( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END
, len( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END ) -1
, 0, '.' ) AS money )
FROM @.MyTable

RowID MyMoney
--
1 -78983.8400
2 78983.8500

For illustration purposes, I've also added a decimal point two places from the right. If you don't need that, then remove the STUFF() function. (I thought that just 'might' come up next...)

You 'should' be able to use a query like this to INSERT the staging table data into your production table.

|||

Ok, Arnie, I'll give it a shot. Thanks again.

John

|||

My brain wasn't operating on all cylinders this morning.

You can do without the STUFF() function -just divide by 100 if you need the partial money, remove the [ / 100 ] if you have whole money.

Code Snippet

SELECT
RowID,
MyMoney = cast( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END AS money ) / 100

FROM @.MyTable

|||This is great, Arnie, thanks.