By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,262 Members | 1,125 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,262 IT Pros & Developers. It's quick & easy.

Change data type during INSERT INTO ?

P: n/a
I'm doing a data conversion project, moving data from one SQL app to
another.
I'm using INSERT INTO with Select and have the syntax correct. But when
executing the script I get:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

Is it possible to change the data type during the INSERT INTO statement?

Thanks

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
The destination table should be compatible with the data type of select
table

Madhivanan

Jul 23 '05 #2

P: n/a
"rdraider" <rd******@sbcglobal.net> wrote in message
news:eR******************@newssvr14.news.prodigy.c om...
I'm doing a data conversion project, moving data from one SQL app to
another.
I'm using INSERT INTO with Select and have the syntax correct. But when
executing the script I get:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

Is it possible to change the data type during the INSERT INTO statement?

Thanks


As the insert into has to guess about the datatypes of the table to create,
the safest way is to specifically create the table prior to the select into.

You can use cast or convert to gain a little more control over the way the
table is created.
but since you didn't post and example I can't help you more than this.
Jul 23 '05 #3

P: n/a
On Fri, 18 Mar 2005 06:48:42 GMT, rdraider wrote:
I'm doing a data conversion project, moving data from one SQL app to
another.
I'm using INSERT INTO with Select and have the syntax correct. But when
executing the script I get:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

Is it possible to change the data type during the INSERT INTO statement?


Hi rdraider,

If you use
INSERT INTO tablename (col1, col2, ..., colN)
SELECT expr1, expr2, ..., exprN
FROM ...
WHERE ...

Then the result of each expression will implicitly be converted to the
datatype of the corresponding column before it's stored in the table.

Your message indicates that one of your expressions is of datetype
varchar, but the corresponding column is of datatype float, and the
implicit conversion failed. E.g. because the varchar value to be
converted happened to be 'xxhasiu'.

If you use INSERT without column list, or SELECT *, then the first thing
should be to add a column list - changes to the table structure might
change the number and order of columns in the INSERT or in the SELECT *,
inducing a mismatch; explicitly naming the columns ensures that this
won't happen.

If that doesn't fix it, then find out which column(s) in the destination
table are of datatype float; run the SELECT (without the INSERT INTO) to
see the results and inspect the output to find the offending value. If
the output has too many rows for visual inspection, you might try adding
AND ISNUMERIC(exprN) = 0
This will return only the rows where exprN can't be converted to int,
float, money or decimal.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.