[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