Data type for money - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data type for money

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-16 : 11:12:36

When a user enter's a dollar amount into the textbox I would like it to be stored as:

5.00
20.00
250.000 etc.

What's the data type I would use?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:14:10

use money itself as datatype

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-16 : 11:20:43

Thanks!

Another question...

I have duplicate rows in a table how do I get rid of them?

I have primary keys on:

Claim and PIC

I would like to dump the non dups into another table.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:22:43

if you already have PK on them, then how will they contain duplicates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-16 : 11:26:36

I'm trying to import an Excel file into a SQL table that has keys on them. The Excel file has duplicates so dumped that data into a different table (without keys) Now I want to remove the dups so I move them into the DB where all of the other data is stored.

I hope that makes sense.

I tried to remove dups from the Excel spreadsheet but it didn't go into my table said I have dups. I have over 2000 rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:30:42

ok. are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-16 : 11:33:25

Yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:38:24

[code]DELETE t
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Claim,PIC ORDER BY Claim)AS Seq FROM YourTable)t
WHERE t.Seq>1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-16 : 11:43:23

Thanks!

Last question...

Now I want to add these rows into my table where I have the primary keys.

Can I do an

insert into Claims select * from test(where I had dups)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:47:04

hey in that case you dont need deletion. you can just do


insert into Claims 
select required columns... 
from
(SELECT ROW_NUMBER() OVER(PARTITION BY Claim,PIC ORDER BY Claim)AS Seq,other columns.... FROM test)t
WHERE t.Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-16 : 11:56:16

Thanks that will save me time.

Can I use select * where you have required columns or do I need to write them all out?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 12:00:20

you need to write them all out,as you now have additional field inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-16 : 12:15:52

Okay thanks so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 12:18:31

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources