Re: Filling Missing Primary Key Values

On Aug 11, 2011, at 15:08, Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:

> On Thu, Aug 11, 2011 at 11:47 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
>> I've a table (from a client, not created here) with a column that should
>> be the primary key, but not all rows have a value for this attribute. The
>> column format is VARCHAR(12) and has a variety of values, such as 96-A000672
>> and 9612-0881 (probably assigned by different analytical laboratories).
>>
>> A simple sequence of numbers would do the job of replacing NULL values.
>> What is the most parsimonious way to replace NULLs with unique values for
>> this column? I also need to add such values for a new set of data that I'm
>> in the process of translating from spreadsheet format to the table
>> structure.
>>

The technical aspect is covered but consider using one or two characters as a prefix related to the data source. You've already taken the hit for using a text data type so you might as well take advantage of it. Even if you have a source field this can be useful. The only costraint is you limit the number of sequence values you can use (per source).

If you use the sequence you can always reset it between imports.

David J.