Hi Sergey,
You are the greatest!
Thanks again
Jim
>Yes, you've to fetch first record before WHILE loop. The second fetch has to be at the end of that loop. Othervise you would process the last record twice.
OPEN translator_cursor
>
>FETCH NEXT FROM translator_cursor INTO @Home_suniq, @Ident
>
>
>set @Nextuniq = @Nextuniq + 1
>While @@FETCH_STATUS = 0
>BEGIN
>
> INSERT INTO translator
> ( suniq, ident,oldsuniq )
> VALUES
> (@nextuniq, @Ident, @Home_suniq)
> set @Nextuniq = @Nextuniq + 1
>
> FETCH NEXT FROM translator_cursor INTO @Home_suniq, @Ident
>
>END
>
>
>>Hello,
>>
>>I wrote my first cursor to translate the old pk (primary key) into the new pk (suniq). The first new pk is 451001830. So I am taking the records that are not already change form a table pretranslator into a cursor and adding the new pk to the translator table along with the old pk and ident which is the system wide PK, suniq is just the local pk. It is a commerial program and I don't know why they have a local and system wide pk.
>>
>>Why am I duplicating the last record
>>
>>suniq ident oldsuniq
>>----------- ---------- -----------
>>451002213 41016837 41016837
>>451002214 41016837 41016837
>>
>>CREATE PROCEDURE sp_new_students
>>@nextuniq INT
>>
>> AS
>>DECLARE @Home_suniq int , @Ident varchar(10)
>>DECLARE translator_cursor CURSOR
>>DYNAMIC
>>FOR
>>-- Get all of the records that are not already updated
>>SELECT
>> jw2.suniq AS home_suniq,
>> jw2.ident
>>FROM
>> dbo.jw2_studemo_suniq_ident as jw2
>>WHERE
>> NOT EXISTS (select * from dbo.catcboe_students where ident = jw2.ident)
>>
>>
>>
>>OPEN translator_cursor
>>-- Do I need to get the first record outside the loop?
>>FETCH NEXT FROM translator_cursor INTO @Home_suniq, @Ident
>>INSERT INTO translator
>> ( suniq, ident,oldsuniq )
>>
>>VALUES
>> (@nextuniq, @Ident, @Home_suniq )
>>
>>
>>-- increment @nextuniq
>>set @Nextuniq = @Nextuniq + 1
>>While @@FETCH_STATUS = 0
>>BEGIN
>>FETCH NEXT FROM translator_cursor INTO @Home_suniq, @Ident
>>
>>INSERT INTO translator
>> ( suniq, ident,oldsuniq )
>>
>>VALUES
>> (@nextuniq, @Ident, @Home_suniq)
>>set @Nextuniq = @Nextuniq + 1
>>
>>
>>
>>END
>>
>>CLOSE translator_cursor
>>DEALLOCATE translator_cursor