06-16-12, 02:49 #1Registered User
- Join Date
- Jun 2012
Unanswered: SQL NULL values to a sequential number (The field is a Nvarchar datatype)
Ok I have upgraded my works database from a poorly designed Access database to a SQL database.
The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
To make things more interesting this field is not a integer type, its a Nvarchar type as its a Hardware ID. Both numerical and characters are require.
06-16-12, 18:21 #2Registered User
- Join Date
- Sep 2001
- Chicago, Illinois, USA
Glad your transition was successful.
As I am sure you are aware, you transited from a "poorly designed Access database" to a poorly designed SQL Server database. The issues you mentioned have nothing to do with whether the database was Access or SQL.
You can easily do what you are envisioning by joining those records to a table with sequential numbers, like the master..spt_values table (where [type]='P') and updating those sequential values, plus your base of 999999900.
Although this is not not terribly difficult, why even make it that complex. Why not just put the number 999999999 in that field for all of those records. Then, any time you see that number, you know it was one of those records. Do you really need uniqueness?Ken
Maverick Software Design
(847) 864-3600 x2