I'm getting a very strange error in SQL Server. Any Ideas?

Factual Questions
1

I’ve stripped this down to about as bare bones as it can be:

SELECT fname, minitial, lname,
CAST(sex as char(1)) as sex
INTO tblsrp_PATIENT
FROM Demographics$

UPDATE tblsrp_PATIENT SET tblsrp_PATIENT.sex = ‘M’
WHERE tblsrp_PATIENT.sex = 1

UPDATE tblsrp_PATIENT SET tblsrp_PATIENT.sex = ‘F’
WHERE tblsrp_PATIENT.sex = 2

SELECT * FROM tblsrp_PATIENT

DROP TABLE tblsrp_PATIENT

GO

The reason for this is that Demographics$ stores sex as a numeric value, with the values 1 or 2. After creating the PATIENT table, I want to convert those to character values ‘M’ or ‘F’. If I don’t CAST sex as some sort of char or varchar, the column will be created as a float, and then I can’t update it to ‘M’ or ‘F’.

If I comment out either of the UPDATE statements, everything runs as expected, but of course only one sex gets updated. If I run both of them, I get this error:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value ‘M’ to a column of data type int.

It doesn’t matter which of the two UPDATE statements comes first, it always bombs on the first one. Even if I update 1=M and 1=M or 2=F and 2=F (or reversing them), it always bombs on the first one. Even stranger, I took out about 10 previous update statements on other fields. It just bombs on this one. (Or rather, these two.)

Any ideas why?

2

Nevermind. Putting the numbers in quotes fixed it.

3

I was just about to say… you’re updating a field as a char, but comparing it to a number. You could get around the original error and avoid the need for an update by doing something like this:


SELECT fname, minitial, lname, 
CASE sex 
WHEN 1 THEN 'M'
WHEN 2 THEN 'F' 
END as sex
INTO tblsrp_PATIENT
FROM Demographics$

That was, you can create your table with the sex column as char, and it’ll automatically insert the correct value based on the lookup that you provide.