473,289 Members | 1,743 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Change data type during INSERT INTO ?

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
3 11998
The destination table should be compatible with the data type of select
table

Madhivanan

Jul 23 '05 #2
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Ray in HK | last post by:
Will it be possible to specify the date format of type DATE during data loading ?
4
by: Richard Cornford | last post by:
For the last couple of months I have been trying to get the next round of updates to the FAQ underway and been being thwarted by a heavy workload (the project I am working on has to be finished an...
5
by: Robert Stearns | last post by:
Either I missed something, or ALTER TABLE does not have this capability. Is there any way of doing it except DROPping all constraints which mention this table, EXPORTing the data, DROPping the...
0
by: André | last post by:
Hi, Still the same (unsolved problem), but other error now. I have a detailsview for inserting data, and a dropdownlist which selectedValue must be used to fill one of the field of the...
17
by: Timothy.Rybak | last post by:
Hello all, This is my first attempt at an application, so kid gloves are appreciated. I need to make a very simple form that only has a few elements. One is TraceCode - a text field that is...
0
by: tom c | last post by:
I am going through "Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control" found at http://msdn2.microsoft.com/en-us/library/sdba1d59.aspx I am using...
0
by: le0 | last post by:
Guys, I create a simple insertion in a database using DetailsView control, when i run this page and try to insert some data, the browser returns an error like this ...
1
by: Doc11 | last post by:
I'm trying to allow users insert data into a database using the form view. But when I click the insert button I get this error: Server Error in '/Customer Database' Application....
4
by: Alexis | last post by:
Hello, I'm facing oracle trigger problem. Anyone can help or advise how to resolve it? Below are the explaination on my problem I've created a trigger for my program. When there is a new...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.