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

CodePudding user response：

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.

CodePudding user response：

use CAST to convert this properly

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

output :

49.00

CodePudding user response：

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