Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Insert Decimal

  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: Insert Decimal

    I mamtrying to insert into a MS SQL 2000 database a decimal
    and nothing works

    i have tried :

    INSERT INTO MyTable
    (myDecimal) VALUES ('1,1')

    INSERT INTO MyTable
    (myDecimal) VALUES ('1.1')

    INSERT INTO MyTable
    (myDecimal) VALUES (CONVERT(decimal('1,1')))

    INSERT INTO MyTable
    (myDecimal) VALUES (CONVERT(decimal('1.1')))

    INSERT INTO MyTable
    (myDecimal) VALUES (CONVERT(decimal(1,1)))

    INSERT INTO MyTable
    (myDecimal) VALUES (CONVERT(decimal(1.1)))



    thank you for helping

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    [code]
    create table #decimal_temp (c1 decimal (10,2), c2 money, c3 float, c4 int)
    declare @var decimal (10,2)
    select @var = 123.4
    insert into #decimal_temp (c1, c2, c3, c4)
    values (@var, @var, @var, @var)

    select c1, c2, c3, c4
    from #decimal_temp
    drop table #decimal_temp

    -- RESULTS
    Code:
     
     
    c1            c2             c3                   c4 
    ------------ -------------- -------------------- ----------- 
    123.40       123.4000       123.40000000000001   123
    (1 row(s) affected)
    Last edited by tomh53; 10-10-06 at 14:43.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    have you tried

    INSERT INTO MyTable
    (myDecimal) VALUES ( 1.1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2005
    Posts
    266
    tomh53 for you a decimal = 1,2
    r937 for you a decimal = 1.2

    if i do
    INSERT INTO MyTable
    (myDecimal, myString) VALUES ( 1,1 , 'any')

    it doesn't work of course

    INSERT INTO MyTable
    (myDecimal, myString) VALUES ( 1.1 , 'any') does not too

    thank you

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    tom your code is too complicated i just want to run an INSERT normally

    thank you

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please script the table layout for us?

    i'm curious as to what "doesn't work" might mean
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by anselme
    tom your code is too complicated i just want to run an INSERT normally

    thank you
    ??????
    It's an EXAMPLE! It shows you that it works if done properly!

    Too complicated my rosy red @$$.

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Dec 2005
    Posts
    266
    CREATE TABLE [MyTable] (
    [id_MyTable] [int] NOT NULL ,
    [Price] [decimal](18, 2) NULL ,
    [Name] [nvarchar] (100) NOT NULL
    ) ON [PRIMARY]


    then :: INSERT INTO MyTable (Price,Name) VALUES ( 0, 'any')

    tomh53 thanks a lot for your help but just to insert there is no need for that code, and I need it for accees 2000 too
    Last edited by anselme; 10-10-06 at 14:59.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What is the default language on this server?
    Code:
    select @@language
    Is this a problem of interpreting a period vs comma as a decimal point?

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by tomh53
    ??????
    It's an EXAMPLE! It shows you that it works if done properly!

    Too complicated my rosy red @$$.

    Easy big fella

    anselme: You do know that you can cut and paste the code into query analyzer and test it.....right?

    If you don't know that, then we have to know...we need to level set some times.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And why is it Rosey?

    Never mind, too much info
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ...and based on your table and SQL, it would not run because your 1 column is NOT NULL, so your INSERT wouldn't even work? Did you test it?

    This works, and gives you the decimals

    Code:
    USE Northwind
    GO
    
    CREATE TABLE [MyTable99] (
    [id_MyTable] [int] NOT NULL ,
    [Price] [decimal](18, 2) NULL ,
    [Name] [nvarchar] (100) NOT NULL 
    ) ON [PRIMARY]
    GO
    
    INSERT INTO MyTable99 (id_myTable, Price,[Name]) VALUES (1, 0, 'any')
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO

    Returns:

    Code:
                                                                                               
    1           .00                  any
    (1 row(s) affected)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    I mamtrying to insert into a MS SQL 2000 database a decimal
    contradiction going on http://www.dbforums.com/showthread.p...=1#post6236317

    Is it SQL 2k or Access?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Dec 2005
    Posts
    266
    both ! I ask it for access and MS SQL

    but is it or not possible to insert a decimal not depending on the server language ?

    if i run a test on the srrver I get a 1,20 (not 1.20)

    INSERT 1,20 cannot work of course

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anselme
    if i run a test on the srrver I get a 1,20 (not 1.20)
    what you get is not always what you give (dates being a classic example)

    Quote Originally Posted by anselme
    INSERT 1,20 cannot work of course
    so what does this tell you about what to give?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •