add and populate UniqueID field

[kmclane]

Hi, normally I search before asking, but the search function is currently unavailble. I just added about two years of data to a table, then realized I need a unique ID field to enable full text indexing. I added the field, but it filled up with Nulls. I tried an update query to populate it with "(newid())" but that didn't work. Do I need to drop the table and start from scratch so the field will populate correctly or is there an easier way?
Thanks, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dbomrrsm]

you can do it with a cursor - example:

declare mycursor cursor for select ref from mytab
OPEN mycursor
FETCH NEXT FROM mycursor
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE mytab
SET UID = (select newid())
WHERE CURRENT OF mycursor
FETCH NEXT FROM mycursor
end
close mycursor
deallocate mycursor

[blue]DBomrrsm[/blue]

 

[kmclane]

I'm really new to this SQLServer, where would I run this code? Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

I have managed to put this in an Access function, create and run a querydef, but it is not updating the entire table, just a small portion of it. Here is my resulting SQL:

declare mycursor cursor for select prikey from ARUMonth
OPEN mycursor
FETCH NEXT FROM mycursor
WHILE @@FETCH_STATUS = 0
BEGIN
Update ARUMonth
SET PriKey = (select newid())
WHERE CURRENT OF mycursor
FETCH NEXT FROM mycursor
End
Close mycursor
deallocate mycursor

I'm in a totally new area, so any help is appreciated.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dbomrrsm]

Your code looks ok except for a few things - does prikey already exist in ARUMonth and if so is it the way of identifying the row in the table.

what the cursor does is look at each row in isolation and then write back to your table using the id from the cursor to identify which row to update. If the prikey is the new field you created to hold the newid() then you cant use it in the cursor declaration - you need something like:

declare mycursor cursor for select (some fields which can id the row) from ARUMonth
OPEN mycursor
FETCH NEXT FROM mycursor
WHILE @@FETCH_STATUS = 0
BEGIN
Update ARUMonth
SET YourNewField = (select newid())
WHERE CURRENT OF mycursor
FETCH NEXT FROM mycursor
End
Close mycursor
deallocate mycursor
[/code]


if you run your code in Query Analyser it should work - dont be too suprised though if it takes a while.

May be worth creating a subset of data in a table and run it against that to check it works first.



[blue]DBomrrsm[/blue]

 

[dbomrrsm]

alternatively ...............

never worked with fulltext indexing but if you only need a unique identifier and an int would work you could simply put an identity field on the table. It should auto fill.
Set the seed to 1 and the increment to 1 and set the field to not allow nulls. Data type of int will work nicly.

This will add a field that automatically incriment every time you put a new record in the table.

[blue]DBomrrsm[/blue]

 

[kmclane]

Does the field I use need to be unique? That's the main resaon I added the new field. I don't have a unique field.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

Well, I went with the alternative method. Seems to have worked fine. Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dbomrrsm]

Im not 100% sure - it probably does have to be as it is used to identify which row to update with the newly created newid(). in the WHERE CURRENT OF mycursor but I am not sure if it knows which row it has grabbed and is therefore looking at or if it does infact need an id to look up the row in the table to be updated - again a test will show whether this is the case or not.


Alternatively, could you not do my second suggestion of adding an increment field then use that in the cursor as the id of the row if you need a newid() type unique identifier for full text indexing.

good luck and let us know how you get on.

[blue]DBomrrsm[/blue]

 

[kmclane]

I used your increment idea, made that my primary key and it worked fine. Do you see any problem doing it that way? I wasn't set on using the newid thin anyway, it's just the first thing I came across. I'm just trying to speed up queries from an Access97 front end. I figure indexing the two fields that are used to search on couldn't hurt.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dbomrrsm]

Nope no problems with that - what you have done sounds fine - and as ever if it works for you and speeds up your queries then great.

[blue]DBomrrsm[/blue]