[dba-SQLServer] Insert problem

Jim Lawrence accessd at shaw.ca
Tue Sep 20 17:06:47 CDT 2005


Hi Doris:

Thank you that. I did discover the error on my own and it was that the value
was calculating, initially to a floating point value and then attempting to
translate into a string value when being inserted, resulting in the strange
results. The SELECT result would display correctly, not present an error
when trying to INSERT but produce a rather bazaar result.

Your solution would have fixed the error.

Thanks
Jim  

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike &
Doris Manning
Sent: Tuesday, September 20, 2005 1:05 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Insert problem

Have you tried casting MyField1 as VarChar(20) as part of the Select
statement?

	INSERT INTO MyTable2 (MyField2)
	SELECT CAST(MyField1 AS VarChar(20)) AS MyFieldName1 FROM MyTable1

Doris Manning
mikedorism at verizon.net

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Tuesday, September 20, 2005 2:47 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Insert problem

Hi All:

I am having a strange SQL problem. It is most likely something that I am
over looking but...

1. The MS SQL version is 2000.
2. The SQL code is an INSERT from a SELECT.
3. The SELECT statement displays the correct results.
4. The results are then INSERTed. 

	INSERT INTO MyTable2 (MyField2)
	SELECT MyField1 AS MyFieldName1 FROM MyTable1

5. The Value from the MyField1 is an assembled string '34700.613217592596'
6. The destination field, MyField2 is a Varchar(20)... plenty of space.

The results in MyTable2, MyField2 is '34700.6'? I can cut and paste the
value '34700.613217592596' in the destination field without error.

It appears that the INSERT process for some reason deems the string value to
be a default Decimal, with one decimal place and strips off the remaining
numbers???

I have never seen this type of conversion error especially when none was
requested or configured. Has anyone else (?); and if so is there a solution?

MTIA
Jim  

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list