different datatypes with UPDATE or INSERT

I'm writing an SP that retrieves data on a linked SQL server, and
selectively updates or inserts like-named rows on the local server. Two
columns on the remote server are Mileage varchar(25) and Price varchar(25),
whereas on the local server the datatypes are INT and MONEY.
As an example of what's needed for 3 sample rows:
Mileage (remote) = 23,456; 'Call for Details'; 56,789
Mileage (local) = 23,456; NULL; 56,789
Price (remote) = $9,995.00; 'Call Us'; $14,900.00
Price (local) = $9,995.00; 'NULL'; $14,900.00
How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
other words, if there are 'non-int' or 'non-money' values coming over from
the remote server, will SQL Server automatically convert these 'invalid'
values to NULL, or do I need to handle it somehow, maybe via a CASE
expression in the UPDATE or INSERT INTO, or...?
Thanks.
Message posted via http://www.webservertalk.comSQL Server will attempt a cast from a character field to a numeric. If it
fails, it will throw an error. A better option would be casting yourself and
logging any failures, including parameters that caused the failure. Humans
can then read the log and correct the data.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"The Gekkster via webservertalk.com" wrote:

> I'm writing an SP that retrieves data on a linked SQL server, and
> selectively updates or inserts like-named rows on the local server. Two
> columns on the remote server are Mileage varchar(25) and Price varchar(25)
,
> whereas on the local server the datatypes are INT and MONEY.
> As an example of what's needed for 3 sample rows:
> Mileage (remote) = 23,456; 'Call for Details'; 56,789
> Mileage (local) = 23,456; NULL; 56,789
> Price (remote) = $9,995.00; 'Call Us'; $14,900.00
> Price (local) = $9,995.00; 'NULL'; $14,900.00
> How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
> other words, if there are 'non-int' or 'non-money' values coming over from
> the remote server, will SQL Server automatically convert these 'invalid'
> values to NULL, or do I need to handle it somehow, maybe via a CASE
> expression in the UPDATE or INSERT INTO, or...?
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||It will try to automatically convert the data from varchar to integer.
However, if any value in the insert is invalid, it will crash. A good way
to handle this is using an Instead Of trigger. Instead of just inserting
the data, you run a check on the data to see if it is valid. Bad data goes
into an exception table, good into the real table.
There are quite a few different routines around to validate that a value is
a reasonable numeric value, but you will likely not want to use isNumeric as
it is very liberal. Search on groups.google.com for isNumeric and you will
see that is covered quite often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"The Gekkster via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:852fc5294d584b37b647b85f7d799e95@.SQ
webservertalk.com...
> I'm writing an SP that retrieves data on a linked SQL server, and
> selectively updates or inserts like-named rows on the local server. Two
> columns on the remote server are Mileage varchar(25) and Price
> varchar(25),
> whereas on the local server the datatypes are INT and MONEY.
> As an example of what's needed for 3 sample rows:
> Mileage (remote) = 23,456; 'Call for Details'; 56,789
> Mileage (local) = 23,456; NULL; 56,789
> Price (remote) = $9,995.00; 'Call Us'; $14,900.00
> Price (local) = $9,995.00; 'NULL'; $14,900.00
> How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
> other words, if there are 'non-int' or 'non-money' values coming over from
> the remote server, will SQL Server automatically convert these 'invalid'
> values to NULL, or do I need to handle it somehow, maybe via a CASE
> expression in the UPDATE or INSERT INTO, or...?
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||You need to test the values in the update, and update the local table to nul
l
when the remote value is not numeric... (This should handle 99.99% of teh
cases
Update <Table> Set
LocalCol = Case IsNumeric(RemoteCol)
When 1 Then Cast (RemoteCol as Integer)
Else Null End
From ...
"The Gekkster via webservertalk.com" wrote:

> I'm writing an SP that retrieves data on a linked SQL server, and
> selectively updates or inserts like-named rows on the local server. Two
> columns on the remote server are Mileage varchar(25) and Price varchar(25)
,
> whereas on the local server the datatypes are INT and MONEY.
> As an example of what's needed for 3 sample rows:
> Mileage (remote) = 23,456; 'Call for Details'; 56,789
> Mileage (local) = 23,456; NULL; 56,789
> Price (remote) = $9,995.00; 'Call Us'; $14,900.00
> Price (local) = $9,995.00; 'NULL'; $14,900.00
> How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
> other words, if there are 'non-int' or 'non-money' values coming over from
> the remote server, will SQL Server automatically convert these 'invalid'
> values to NULL, or do I need to handle it somehow, maybe via a CASE
> expression in the UPDATE or INSERT INTO, or...?
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||That's the same conclusion I came to. Even though IsNumeric may not be
'ideal' it seems to serve the purpose here well.
Thanks to all for the input.
Message posted via http://www.webservertalk.com