SQL - Insert Into - Convert Datatype

Question

my table consists of the following fields:

IDTemp int NO NULL
Nr_ nvarchar(50) NULL
Description nvarchar(50) NULL
[Description 2] nvarchar(50) NULL
Price money NULL

Now I want to insert some values into that table from another database, unfortunately the price values from the other database are stored as a nvarchar and not a money value. It looks a bit like this: 49.0000000000

If I insert this value into my money field, it stays empty. My question is, how can I use INSERT INTO and convert my price value so that it goes into my money field and doesn't have 10 zeroes?

INSERT INTO TempSaveArticle (Nr_, Description, [Description 2], Price)
    VALUES (123456789, Yarn, blue, '49.0000000000')

Thanks for your help in advance

Avoid `money` it has rounding problems https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type and https://stackoverflow.com/questions/1355418/what-data-type-should-i-use-to-store-monetary-values Also numbers shold not have quotes — Charlieface, Mar 21 '22 at 13:01

score 0 · Accepted Answer · answered Mar 21 '22 at 07:32

Use CONVERT().

INSERT INTO TempSaveArticle (Nr_, Description, [Description 2], Price) 
VALUES (123456789, Yarn, blue, CONVERT(MONEY, 49.0000000000))

Ideally you'd want to do this more dynamically.

INSERT INTO TempSaveArticle (Nr_, Description, [Description 2], Price) 
SELECT myTable.*
FROM myTable
WHERE myTable.ID = 123 OR <<some other conditions>>

MONEY is effectively a DECIMAL.

You can CAST() or CONVERT() it to and from Decimal <--> Money. The only difference I know, is that MONEY is displayed by default using your computers' Language settings. But in my opinion, don't use MONEY, stick with DECIMALS and display it how you want using FORMAT(). More on this argument here.

@qawas you might have to edit your OP and show the 49.00000 inside single quotes, otherwise people will think you're not trying to insert a character string, but an actual number, which is why I answered it the way I did. — Fandango68, Mar 21 '22 at 08:11

score 0 · Answer 2 · 2022-03-21T08:00:44.750

We have to use cast or convert, this works directly for datatype CHAR, VARCHAR, NCHAR and NVARCHAR.
SQL server will not convert directly from TEXT to money. If the datatype is TEXT we have to first cast to varchar(for example) and then cast again to money.
NB: In some countries, for example France where I leave, it is standard practise to use a comma instead of a point. If there is a possibility of this we would need to use REPLACE first otherwise we will get an error.

create table mon ( 
monet text, monev nvarchar(20),
monetm money, monevm money);
GO

✓

insert into mon (monet, monev) values('49.0000000000','49.0000000000');
GO

1 rows affected

select * from mon;
GO

monet         | monev         | monetm | monevm
:------------ | :------------ | -----: | -----:
49.0000000000 | 49.0000000000 |   null |   null

update mon set monevm = cast(monev as decimal(20,10))from mon;
GO

1 rows affected

update mon set monetm = cast(monet as decimal(20,10))from mon;
GO

Msg 529 Level 16 State 2 Line 1

Explicit conversion from data type text to decimal is not allowed.

update mon set monetm = cast(
                        cast(monet as varchar)
                        as decimal(20,10))from mon;
GO

1 rows affected

select * from mon;
GO

monet         | monev         |  monetm |  monevm
:------------ | :------------ | ------: | ------:
49.0000000000 | 49.0000000000 | 49.0000 | 49.0000

db<>fiddle here

Great answer, but his OP didn't show the value inside a string, or as text, so I answered it as a direct CONVERT() — Fandango68, Mar 21 '22 at 07:52

score 0 · Answer 3 · answered Mar 21 '22 at 07:51

0

use CAST to convert this properly

SELECT CAST ('49.0000000000' as numeric(10,2));

output :

49.00

answered Mar 21 '22 at 07:51

Vineesh Vijayan

570
4
15

SQL - Insert Into - Convert Datatype

3 Answers3