Hi,
I have a strange one. I am using the datatype DECIMAL in my SQL table. like this:
DebtAmount DECIMAL(17,2) NULL
If I insert values like: 100 or 10.5 or -10 they insert no problem.
The problem I am having is loads of my data has negative values that have it like 200- or 350- (you see the minus sign at the end of the value instead of before it)
When i bring them into excel they come in no problem with the minus sign before the value but SQL does not like the minus figure after the value. i cant really do anything about how the values import from our system this way so i was hoping i could work with sql on this issue.
Thanks
G
March 25, 2024 at 2:10 pm
What tool are you using to import? Can it 'look for' trailing minus signs and move them to be leading as part of the import?
Or perhaps you will have to change the target column's datatype - VARCHAR(30), perhaps – and then you can manipulate and CAST in T-SQL.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 25, 2024 at 2:14 pm
Hi Phil,
I export data into a txt file and then i am using the BULK INSERT command.
I was hoping i could find a fix without touching the raw data.
i have many values in the data - should i just get rid of DECIMAL datatype and use VARCHAR instead?
Are there any dangers of doing this?
March 25, 2024 at 2:18 pm
You could insert them all as text columns then run another SQL process to convert them to your required format into a different table.
March 25, 2024 at 2:46 pm
i have many values in the data - should i just get rid of DECIMAL datatype and use VARCHAR instead? Are there any dangers of doing this?
No danger at all. But you should do as Jonathan suggests and build a subsequent process which translates the values to a table with the correct datatypes (and, importantly, notifies you of any errors in performing that translation).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 25, 2024 at 2:48 pm
ok guys thank you i will try this out!
March 25, 2024 at 4:02 pm
ok so i have two tables - first one takes in the values with the trailing minus sign into a VARCHAR datatype. i then run this over the value:
UPDATE tableA
SET value = '-' + SUBSTRING(value, 1, LEN(value) - 1)
WHERE value LIKE '%-';
i then have the minus sign in front of the value. I then run a INSERT from one table into the other.
insert into tableb (Value)
select value from tableA;
i think this will have to do me for the moment. seems the less hassle option
March 25, 2024 at 6:24 pm
Can I ask how I could achieve notifications of errors during this translation?
March 25, 2024 at 6:32 pm
It should be more efficient to do that using a trigger rather than doing a separate UPDATE after the load. The trigger would also allow for indirect notification (you wouldn't want to do direct notification from a trigger, but the trigger could insert to an error table that could cause a notification.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 25, 2024 at 6:33 pm
Can I ask how I could achieve notifications of errors during this translation?
What automation tool will you be using for this process?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 25, 2024 at 6:40 pm
I'm not sure. I was thinking of some sort of task scheduler in mssql.
March 25, 2024 at 8:47 pm
If using SQL Agent ...
One way is to create a log table and log the errors there and then make sure that the Agent job fails if one or more errors occurred, sending you an e-mail notification on job failure.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 26, 2024 at 8:30 am
OK Phil thank you - I will investigate this. Thanks for your assistance.
March 26, 2024 at 8:53 am
oh one more quick question - what size should I make the VARCHAR datatpe that will hold the decimal value my temp table? Or would TEXT datatype do the job of just holding the value?
Thanks
oh one more quick question - what size should I make the VARCHAR datatpe that will hold the decimal value my temp table? Or would TEXT datatype do the job of just holding the value?
Thanks
The TEXT datatype is deprecated, so leave that one well alone.
Make the VARCHAR column wide enough to accommodate the widest possible number you would expect to see in your source file.
Note that I said widest, not largest: 1.23456789 is wider than 9999.99, for example.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply