Inserting varchar to Decimal/Numeric

Hi,

Can we insert varchar values to Decimal(9,2)/numeric(9,2) fields?

I've a temp table with varchar values. I checked using isnumeric and all the values are numeric. But when I do the insert, it fails.

I was able to insert the following values:

000290165
000501075
000290165
000326314

But NOT the following:

010773474

All the values are 9 digits only.......

How can I convert this kind of numbers?

I need to convert all the 9 digit numbers to XXXXXXX.xx format.

(7digits.2 digits)

Thanks,

Siva.

I believe that in this case your expectations are incorrect. When I run:

Code Snippet

select inpt,
convert(numeric(9,2), inpt)
as convertedValue
from ( select '000290165' as inpt union all
select '000501075' union all
select '000290165' union all
select '000326314' union all
select '010773474'
) a

/*
inpt convertedValue
--
000290165 290165.00
000501075 501075.00
000290165 290165.00
000326314 326314.00

(5 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
*/

The output is as expected. But I don't think that is what you are expecting. To get this correct try:

Code Snippet

select inpt,
convert(numeric(9,2), left(inpt,7) + '.' + right(inpt,2))
as convertedValue
from ( select '000290165' as inpt union all
select '000501075' union all
select '000290165' union all
select '000326314' union all
select '010773474'
) a

/*
inpt convertedValue
--
000290165 2901.65
000501075 5010.75
000290165 2901.65
000326314 3263.14
010773474 107734.74
*/

|||

Wow!! Great!

It worked like a magic!!

Thanks much...

Siva.

Inserting Records from Table type to Temp table

Hi,
I want to insert records from table type to temp table without using cursors
in SQL Server 2000 stored procedure.
Regards,
ShanmugamYou mean like below?
DECLARE @.t TABLE (c1 int)
INSERT INTO @.t (c1) VALUES(1)
CREATE TABLE #t (c1 int)
INSERT INTO #t
SELECT c1 FROM @.t
SELECT * FROM @.t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uma" <uma@.cspl.com> wrote in message news:OZAWvwpHGHA.3936@.TK2MSFTNGP12.phx.gbl...arkred">
> Hi,
> I want to insert records from table type to temp table without using curso
rs
> in SQL Server 2000 stored procedure.
> Regards,
> Shanmugam
>
>

Inserting Records from Table type to Temp table

Hi,
I want to insert records from table type to temp table without using cursors
in SQL Server 2000 stored procedure.
Regards,
ShanmugamInsert into #temp(columns)
Select columns from @.tab
Madhivananl|||Insert into #temp(columns)
Select columns from @.tab
Madhivanan|||Insert into #temp(columns)
Select columns from @.tab
Madhivanan|||Thanks.
Is it possible to give like below.
DECLARE @.a VARCHAR(50)
SET @.a = '#temp'
Insert into @.a(columns)
Select columns from @.tab
any other way?
Regards,
Shanmugam
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1138177782.869581.254030@.z14g2000cwz.googlegroups.com...
> Insert into #temp(columns)
> Select columns from @.tab
> Madhivanan
>|||You'd have to use dynamic SQL for that. Why don't you know the names of your
objects at design time?
ML
http://milambda.blogspot.com/

Inserting records back into temp table that don't exist

I have a procedure that I'm trying to produce for a client. They want to see
total order counts each day from the 1st to the end of the month.
The procedure I have now will produce order counts for days that do exist. I
t
is using the order open date to pull orders from the orders table from
between a start and end date.
The client wants to see zero for the days that didn't have any activity.
For example:
Office Day Total
BranchA 1 50
BranchA 2 0
BranchA 3 10
How do I insert a blank record into my temp table that will show 0 for the
days that didn't pull?
Thanks so much in advance.
Message posted via http://www.webservertalk.comA general method is to create a dataset/table/view with all the days you
want to include and use an OUTER JOIN to get the results. If you want
specific query, please read www.aspfaq.com/5006 and post relevant
information to repro your problem.
Anith