sqlsql-serversql-insertsql-convert

SQL - Insert Into - Convert Datatype

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

Solution

  • 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.