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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply