|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
different datatypes with UPDATE or INSERTselectively 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. SQL 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! *************************** Show quote "The Gekkster via SQLMonster.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.sqlmonster.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. -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "The Gekkster via SQLMonster.com" <forum@nospam.SQLMonster.com> wrote in message news:852fc5294d584b37b647b85f7d799e95@SQLMonster.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.sqlmonster.com You need to test the values in the update, and update the local table to null
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 ... Show quote "The Gekkster via SQLMonster.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.sqlmonster.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.  |
|||||||||||||||||||||||
Other interesting topics