I'm trying to run a data-validation query in SQL Server, but am not sure
how to do it.

create table testTable (ValidatedID INTEGER, EnteredValue NVARCHAR(30))

UPDATE testTable SET ValidatedID=CAST(EnteredValue AS INTEGER) WHERE
ISNUMERIC(EnteredValue)=1 AND CAST(EnteredValue AS INTEGER) IN (SELECT
ValidID FROM tableOfValidIDs)

When I do this, if there is an EnteredValue like '10420036203' that is
too big, I get an overflow error:

The conversion of the nvarchar value '10420036203' overflowed an int column.
Maximum integer value exceeded.

How can I set ValidatedID to EnteredValue only where it is a valid
integer and it exists in the tableOfValidIDs?  I suppose I could convert
  the ValidID's in tableOfValidIDs to NVARCHAR to do the check, but then
I would lose the efficiency of the indexed lookup.

John Smith wrote:
Show quoteHide quoteNevermind, I figured out a solution:

UPDATE testTable SET ValidatedID=EnteredValue WHERE
EnteredValue NOT LIKE '%[^0-9]%' AND DATALENGTH(EnteredValue) <= 9
AND EnteredValue IN (SELECT ValidID FROM tableOfValidIDs)

This effectively converts the varchar value to an integer. It limits the
size of the integer from a little over 2 billion to 1 billion, but this
table will never have that many records.

How about creating a view, instead of storing two copies of the same number,
and having to run this update statement constantly?


CREATE TABLE dbo.testTable
(
EnteredValue NVARCHAR(32)
)
GO

SET NOCOUNT ON
INSERT dbo.testTable SELECT 'foo'
INSERT dbo.testTable SELECT '5'
INSERT dbo.testTable SELECT '8'
INSERT dbo.testTable SELECT '123456789012345'
GO

CREATE TABLE dbo.tableOfValidIDs
(
ValidID BIGINT
)
GO
INSERT dbo.tableOfValidIDs SELECT 5
INSERT dbo.tableOfValidIDs SELECT 123456789012345
GO


CREATE VIEW dbo.ValidEntries
AS
    SELECT EnteredValue
    FROM
        (SELECT
            EnteredValue = CONVERT(BIGINT, EnteredValue)
        FROM
            TestTable
        WHERE dbo.IsReallyInteger(EnteredValue)=1) x
        -- see http://www.aspfaq.com/2390
    INNER JOIN
        tableOfValidIDs v
    ON x.EnteredValue = v.ValidID
GO


Now you can use the view to show *only* valid entries, instead of relying on
congruence between two columns in the same table.  You are also not limited
to integers or some artificial limit on the number of characters in the
string.  And as a bonus, no update statement necessary, unless you are
actually changing a value within the testTable table.

NON-DETERMINISTIC?
Time Difference Calculations
Customers & Phones
Return only the Third Row
Choosing one record over another